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
) } [, ...] [ FROMfrom_item
[, ...] ] [ WHEREcondition
| WHERE CURRENT OFcursor_name
] [ RETURNING * |output_expression
[ [ AS ]output_name
] [, ...] ]
UPDATE
は、条件を満たす全ての行の指定した列の値を変更します。
SET
句には、変更する列のみを指定する必要があります。
SET
句にて明示的に指定されなかった列の値は変更されません。
データベース内の他のテーブルの情報を使用してテーブルを変更するには、2つの方法があります。
1つは副問い合わせを使用する方法、もう1つはFROM
句で追加のテーブルを指定する方法です。
どちらの方法が適切であるかは状況次第です。
RETURNING
句を指定すると、UPDATE
は実際に更新された各行に基づいて計算された値を返すようになります。
そのテーブルの列およびFROM
で指定された他のテーブルの列を使用した式を計算することができます。
テーブル列の新しい(更新された後の)値が使用されます。
RETURNING
リストの構文はSELECT
の出力リストと同一です。
更新を行うためには、そのテーブルまたは少なくとも更新対象の列についてUPDATE
権限を持たなければなりません。
またexpressions
やcondition
で値を読み込む列に対するSELECT
権限も必要になります。
with_query
WITH
句によりUPDATE
問い合わせ内で名前で参照可能な1つ以上の副問い合わせを指定することができます。
7.8とSELECTを参照してください。
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になります)。
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行のみが使用されますが、どの行が使用されるかは簡単には予測できません。
このような不定性の問題があるため、他テーブルの参照は副問い合わせ内のみに留めておいた方がより安全です(ただし、結合よりも可読性や実行速度は低下します)。
パーティションテーブルの場合、行を更新することによって含んでいるパーティションのパーティション制約を満たさなくなることがありえます。
その場合、この行がそのパーティション制約を満たす他のパーティションがパーティションツリー内にあれば、行はそのパーティションに移されます。
もし、そのようなパーティションがなければ、エラーが発生します。
舞台裏では、行の移動は実際はDELETE
とINSERT
操作です。
移される行に対して同時に実行されるUPDATE
やDELETE
のために直列化の失敗エラーになる可能性があります。
セッション1がパーティションキーに対してUPDATE
を実行中であるとしましょう。一方、同時に実行しているセッション2に対してこの行は可視であり、セッション2はこの行に対してUPDATE
またはDELETE
操作をするとしましょう。
その場合、セッション2のUPDATE
またはDELETE
は、行の移動を検出し、直列化の失敗エラー(常にSQLSTATE値が'40001'で返る)を発生させます。
これが起きた場合には、アプリケーションはトランザクションを再試行すると良いでしょう。
テーブルがパーティション化されていない、または、行の移動がない通常の場合には、セッション2は新しく更新された行を特定し、この新しい行のバージョンに対してUPDATE
/DELETE
を実行します。
(外部データラッパがタプルルーティングをサポートしていれば)行をローカルパーティションから外部テーブルパーティションへ移動できますが、外部テーブルパーティションから別のパーティションに移動できないことに注意してください。
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 salesmen WHERE salesmen.id = accounts.sales_id);
同じような結果は結合を使っても得ることができます。
UPDATE accounts SET contact_first_name = first_name, contact_last_name = last_name FROM salesmen WHERE salesmen.id = accounts.sales_id;
ただし、salesmen
.id
が一意キーでない場合、2番目の問い合わせは予期しない結果をもたらすかもしれません。
一方で、最初の問い合わせは、複数のid
がマッチしたときはエラーを発生することが保証されます。
また、あるaccounts
.sales_id
エントリにマッチするレコードがない場合、最初の問い合わせは対応する名前フィールドを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の拡張です。
UPDATE
でWITH
が使用可能であることも同様に拡張です。
他のデータベースシステムには、FROM
オプション内で、対象テーブルが再度指定されることを前提として動作するものもあります。
これはPostgreSQLにおけるFROM
の解釈方法とは異なります。
この拡張機能を使用するアプリケーションを移植する時は注意してください。
標準に従うと、括弧内の対象列名の部分リストに対する入力値は、正しい数の列を生成する任意の行値による式です。
PostgreSQLでは入力値として、行コンストラクタあるいはsub-SELECT
しか許していません。
行コンストラクタを使う場合、個々の列の更新値をDEFAULT
として指定することができますが、sub-SELECT
の内部ではできません。