★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

UPDATE

UPDATE — テーブルの行を更新する

概要

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

説明

UPDATEは、条件を満たす全ての行の指定した列の値を変更します。 SET句には、変更する列のみを指定する必要があります。 SET句にて明示的に指定されなかった列の値は変更されません。

データベース内の他のテーブルの情報を使用してテーブルを変更するには、2つの方法があります。 1つは副問い合わせを使用する方法、もう1つはFROM句で追加のテーブルを指定する方法です。 どちらの方法が適切であるかは状況次第です。

RETURNING句を指定すると、UPDATEは実際に更新された各行に基づいて計算された値を返すようになります。 そのテーブルの列およびFROMで指定された他のテーブルの列を使用した式を計算することができます。 テーブル列の新しい(更新された後の)値が使用されます。 RETURNINGリストの構文はSELECTの出力リストと同一です。

更新を行うためには、そのテーブルまたは少なくとも更新対象の列についてUPDATE権限を持たなければなりません。 またexpressionsconditionで値を読み込む列に対するSELECT権限も必要になります。

パラメータ

with_query

WITH句によりUPDATE問い合わせ内で名前で参照可能な1つ以上の副問い合わせを指定することができます。 7.8SELECTを参照してください。

table_name

更新対象のテーブルの名前です(スキーマ修飾名でも可)。 テーブルの前にONLYを指定すると、指名されたテーブルでのみマッチする行が更新されます。 ONLYを指定しないと、指名したテーブルから継承されたすべてのテーブルでもマッチする行が同時に更新されます。 オプションで、テーブル名の後に*を指定して、明示的に子テーブルが含まれることを示すこともできます。

alias

対象テーブルの代替名です。 別名が指定されると、テーブルの実際の名前は完全に隠蔽されます。 たとえば、UPDATE foo AS fでは、UPDATE文の残りの部分ではfooではなくfとしてこのテーブルを参照しなければなりません。

column_name

table_nameで指名されたテーブル内の列名です。 必要に応じて、列名を副フィールド名や配列の指示子で修飾することも可能です。 対象列の指定にはテーブル名を含めないでください。 たとえば、UPDATE table_name SET table_name.col = 1は無効です。

expression

列に代入する式です。 この式では、テーブル内の対象列やその他の列の変更前の値を使用することができます。

DEFAULT

列にデフォルト値を設定します(デフォルト式が割り当てられていない場合はNULLになります)。 IDENTITY列には関連するシーケンスにより生成された新しい値が設定されます。 生成列に対して、これを指定することは許されていますが、単に生成式から列を計算するという普通の振る舞いを指定するだけです。

sub-SELECT

その前の括弧内の列リストに列挙されているのと同じ数の出力列を生成するSELECT副問い合わせです。 副問い合わせは実行時に最大でも1行しか生成してはいけません。 1行だけ生成されたときは、各列の値が対象の列に代入されます。 1行も生成されなかったときは、対象の列にNULL値が代入されます。 副問い合わせは、更新対象のテーブルの現在行の古い値を参照することができます。

from_item

WHERE条件や更新用の式において、他のテーブルの列を指定するために使用するテーブル式です。 これはSELECT文のFROM句と同じ文法を使います。例えば、テーブル名の別名が指定できます。 自己結合を行う場合を除き、from_itemに更新対象のテーブルを繰り返してはいけません(自己結合を行う場合は、from_item内で更新対象のテーブルとその別名を指定しておく必要があります)。

condition

boolean型の値を返す式です。 この式がtrueを返す行のみが更新されます。

cursor_name

WHERE CURRENT OF条件で使用されるカーソルの名前です。 更新対象の行は、そのカーソルからもっとも最近に取り出された行です。 カーソルはUPDATEの対象テーブルに対するグループ化のない問い合わせでなければなりません。 WHERE CURRENT OFを論理条件といっしょに指定することはできません。 WHERE CURRENT OF付きのカーソル使用に関する情報についてはDECLAREを参照してください。

output_expression

各行を更新した後に計算され、UPDATEによって返される式です。 この式には、table_nameまたはFROMで指定したテーブル(複数可)の任意の列名を使用することができます。 すべての列を返す場合は*と記載してください。

output_name

返される列で使用される名前です。

出力

正常に処理が終わると、UPDATEコマンドは以下の形式のコマンドタグを返します。

UPDATE count

countは、合致したが変更されなかった行を含む、更新された行数を意味します。 BEFORE UPDATEトリガにより更新が抑制された場合に、conditionに合致した行数より少なくなる可能性があることに注意してください。 countが0の場合はconditionに一致する行がなかったことを意味します (これはエラーとはみなされません)。

UPDATEコマンドがRETURNING句を持つ場合、その結果は、RETURNINGリストで定義した列と値を持ち、そのコマンドで更新された行全体に対して計算を行うSELECT文の結果と似たものになるでしょう。

注釈

FROM句が存在する場合、基本的に、対象テーブルとfrom_itemリストで指定されたテーブルが結合され、この結合の出力行が対象テーブルの更新操作の結果となります。 FROM句を使用する場合、更新対象テーブルの1行に対して、結合結果が複数行にならないように注意してください。 言い換えると、対象テーブルの個々の行は、他テーブルの複数の行と結合すべきではありません。 結合結果が複数行になった場合、対象行の更新には結合結果のいずれか1行のみが使用されますが、どの行が使用されるかは簡単には予測できません。

このような不定性の問題があるため、他テーブルの参照は副問い合わせ内のみに留めておいた方がより安全です(ただし、結合よりも可読性や実行速度は低下します)。

パーティションテーブルの場合、行を更新することによって含んでいるパーティションのパーティション制約を満たさなくなることがありえます。 その場合、この行がそのパーティション制約を満たす他のパーティションがパーティションツリー内にあれば、行はそのパーティションに移されます。 もし、そのようなパーティションがなければ、エラーが発生します。 舞台裏では、行の移動は実際はDELETEINSERT操作です。

移される行に対して同時に実行されるUPDATEDELETEのために直列化の失敗エラーになる可能性があります。 セッション1がパーティションキーに対してUPDATEを実行中であるとしましょう。一方、同時に実行しているセッション2に対してこの行は可視であり、セッション2はこの行に対してUPDATEまたはDELETE操作をするとしましょう。 その場合、セッション2のUPDATEまたはDELETEは、行の移動を検出し、直列化の失敗エラー(常にSQLSTATE値が'40001'で返る)を発生させます。 これが起きた場合には、アプリケーションはトランザクションを再試行すると良いでしょう。 テーブルがパーティション化されていない、または、行の移動がない通常の場合には、セッション2は新しく更新された行を特定し、この新しい行のバージョンに対してUPDATE/DELETEを実行します。

(外部データラッパーがタプルルーティングをサポートしていれば)行をローカルパーティションから外部テーブルパーティションへ移動できますが、外部テーブルパーティションから別のパーティションに移動できないことに注意してください。

あるパーティションから別のパーティションへ行を移動しようとしても、UPDATE問い合わせで指定された祖先とは異なる移動元のパーティションの祖先を外部キーが直接参照していることが判明した場合、失敗します。

filmsテーブルのkind列にあるDramaという単語をDramaticに変更します。

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

weatherテーブルの特定の行に対し、気温に関する項目を調整し、降水量をデフォルト値に戻します。

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03';

同じ操作を行い、更新された項目を返します。

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

もう一つの方法である列リスト構文を使用して同じ更新を行います。

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

FROM句の構文を使用して、Acme Corporationを顧客とするセールスパーソンのセールスカウントを1増加させます。

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

WHERE句で副問い合わせを使用して、同じ操作を行います。

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

accountsテーブルのコンタクト先の氏名を、現在アサインされているセールスパーソンと一致するよう更新します。

UPDATE accounts SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM employees
     WHERE employees.id = accounts.sales_person);

同じような結果は結合を使っても得ることができます。

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

ただし、employees.idが一意キーでない場合、2番目の問い合わせは予期しない結果をもたらすかもしれません。 一方で、最初の問い合わせは、複数のidがマッチしたときはエラーを発生することが保証されます。 また、あるaccounts.sales_personエントリにマッチするレコードがない場合、最初の問い合わせは対応する名前フィールドをNULLに設定しますが、2番目の問い合わせは、その行を全く更新しません。

summaryテーブルの統計情報を現在のデータに合うように更新します。

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
     WHERE d.group_id = s.group_id);

新しい商品とその在庫数を挿入します。 既にその商品が存在している場合は、代わりに既存商品の在庫数を更新します。 トランザクション全体が失敗することがないようにこの操作を行うには、セーブポイントを使用してください。

BEGIN;

-- 何かしらの他の操作を行います。
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');

-- 上記のコマンドが一意キー違反により失敗したとします。
-- この場合、次のコマンドを実行します。
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';

-- 他の操作を続けた後、最後に次を実行します。
COMMIT;

filmsテーブルにおいて、c_filmsカーソルが現在位置している行のkind列を変更します。

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

互換性

このコマンドは標準SQLに準拠しています。 ただしFROM句およびRETURNING句はPostgreSQLの拡張です。 UPDATEWITHが使用可能であることも同様に拡張です。

他のデータベースシステムには、FROMオプション内で、対象テーブルがFROM内に再度指定されることを前提として動作するものもあります。 これはPostgreSQLにおけるFROMの解釈方法とは異なります。 この拡張機能を使用するアプリケーションを移植する時は注意してください。

標準に従うと、括弧内の対象列名の部分リストに対する入力値は、正しい数の列を生成する任意の行値による式です。 PostgreSQLでは入力値として、行コンストラクタあるいはsub-SELECTしか許していません。 行コンストラクタを使う場合、個々の列の更新値をDEFAULTとして指定することができますが、sub-SELECTの内部ではできません。