SQL練習室
← SQLガイド

PostgreSQLのUPDATE … FROMとは?別の表の値で更新する書き方と注意点

UPDATE … FROMは、別の表の値を使って表の行を書き換えるPostgreSQLの書き方です。新しい価格の一覧で商品の価格をそろえる、販売記録の合計で在庫を減らす、といった更新を、SETにその表の列をそのまま書いて行えます。標準SQLではなくPostgreSQLの拡張なので、他のDBの書き方との違いにも注意が必要です。

例で使う表

productsは商品1件が1行で、idは商品番号、nameは商品名、priceは価格(円)、stockは在庫数です。price_updatesは新しい価格の一覧で、product_idがproductsのidに対応します。product_idが9の行は、productsに該当する商品がありません。salesは販売記録で、同じ商品が複数行に出てきます。各節では実行前の状態から更新し、更新後のproductsをid順に表示します。

products:商品5件(更新前)
idnamepricestock
1ノート20050
2ペン120100
3消しゴム800
4定規15030
5付箋1000
price_updates:新しい価格の一覧5件
product_idnew_price
1220
2130
390
4140
9500
sales:販売記録4件
product_idquantity
15
110
220
410

UPDATE … FROMの基本構文

sql
UPDATE products
SET price = u.new_price
FROM price_updates AS u
WHERE products.id = u.product_id;

FROMで別の表を指定して、その値で更新する

UPDATEの後ろにFROMを書くと、更新先の表とその表を結合して、結合後の各行ごとに更新できます。SETの右辺にはFROMの表の列を使え、結合の条件はWHEREに書きます。ここではid 1・2・3・4の価格が新しい価格に変わります。price_updatesに無いid 5は結合に一致しないので、更新されず100円のままです。

sql
UPDATE products
SET price = u.new_price
FROM price_updates AS u
WHERE products.id = u.product_id;

SELECT * FROM products ORDER BY id;
実行結果
idnamepricestock
1ノート22050
2ペン130100
3消しゴム900
4定規14030
5付箋1000

結合条件に絞り込みを足して一部だけ更新する

結合条件のあとにANDで条件を足すと、更新する行をさらに絞れます。条件にはFROMの表の列も、更新先の表の列も使えます。ここでは在庫のある商品だけを更新するため、在庫0のid 3は新しい価格の候補があっても80円のままです。結合に一致しない行と同じく、条件を満たさない行は変更されません。

sql
UPDATE products
SET price = u.new_price
FROM price_updates AS u
WHERE products.id = u.product_id
  AND products.stock > 0;

SELECT * FROM products ORDER BY id;
実行結果
idnamepricestock
1ノート22050
2ペン130100
3消しゴム800
4定規14030
5付箋1000

相関サブクエリで同じ更新を書いて比べる

FROMを使わず、SETの右辺にサブクエリを書く方法でも同じ結果になります。ただし、サブクエリは一致する行が無いとNULLを返すので、WHEREで「price_updatesに行がある商品だけ」に絞る必要があります。公式ドキュメントも、FROMの結合は一致が複数あると結果が予測できなくなるため、他の表をサブクエリだけで参照する方が安全だが読みにくく遅いことが多い、と説明しています。

sql
UPDATE products
SET price = (
  SELECT u.new_price
  FROM price_updates AS u
  WHERE u.product_id = products.id
)
WHERE EXISTS (
  SELECT 1
  FROM price_updates AS u
  WHERE u.product_id = products.id
);

SELECT * FROM products ORDER BY id;
実行結果
idnamepricestock
1ノート22050
2ペン130100
3消しゴム900
4定規14030
5付箋1000

集計した結果を使って更新する

集計が必要なときは、FROMにサブクエリ(派生表)を書きます。salesを商品ごとにSUMでまとめてから結合するので、更新先の1行に結合される行が1行になります。集計せずにsalesを直接結合すると、id 1のように複数行ある商品でどの行が使われるか決まりません。ここでは在庫から販売数量の合計を引き、id 1は50-15で35、id 2は100-20で80、id 4は30-10で20になります。

sql
UPDATE products
SET stock = products.stock - s.sold
FROM (
  SELECT product_id, SUM(quantity) AS sold
  FROM sales
  GROUP BY product_id
) AS s
WHERE products.id = s.product_id;

SELECT * FROM products ORDER BY id;
実行結果
idnamepricestock
1ノート20035
2ペン12080
3消しゴム800
4定規15020
5付箋1000

別名の付け方とMySQLなど他DBとの書き方の違い

更新先にも別名を付けられ、WHEREでは別名で参照します。一方、SETの左側の列名には更新先の表名や別名を付けられません。ここでは現在より高い新価格のときだけ更新するので、値下げ(150円から140円)のid 4は変わりません。MySQLはUPDATEの直後にJOINを書く形、SQL ServerはFROMの中で更新先の表をもう一度書く形で、どちらもPostgreSQLとは異なります。PostgreSQLのFROMで更新先の表を再掲すると、別の行との自己結合になります。

sql
UPDATE products AS p
SET price = u.new_price
FROM price_updates AS u
WHERE p.id = u.product_id
  AND u.new_price > p.price;

SELECT * FROM products ORDER BY id;
実行結果
idnamepricestock
1ノート22050
2ペン130100
3消しゴム900
4定規15030
5付箋1000

UPDATE … FROMでよくある間違い

結合条件を書き忘れると、全商品が同じ価格になることがある

WHEREで結合条件を書かないと、すべての商品とすべての新価格の組み合わせができ、更新先の1行に複数行が結合されます。この場合どの行の値が使われるかは決まっておらず、実際には全商品が同じ価格になることが多くなります。エラーも出ないので、更新後の表を見るまで気づけません。FROMを書いたら、更新先との結合条件が必ずあるか確認します。

sql
UPDATE products
SET price = u.new_price
FROM price_updates AS u;

SELECT * FROM products ORDER BY id;

相関サブクエリでWHEREを省くと、一致しない行がNULLになる

SETの右辺をサブクエリにしてWHEREを書かないと、全行が更新対象になります。price_updatesに無い商品はサブクエリがNULLを返すため、id 5の価格がNULLに書き換わります。NOT NULL制約のある列では、エラーで文全体が失敗します。「一致する行だけ」に絞るWHERE EXISTSが必要です。

sql
UPDATE products
SET price = (
  SELECT u.new_price
  FROM price_updates AS u
  WHERE u.product_id = products.id
);

SELECT * FROM products ORDER BY id;

実際に1問解いてみる

UPDATE FROM

salary_revisions の新しい給与(new_salary)で、employees の給与(salary)を更新してください。社員は emp_id と id で対応します。

テーブル

読み込み中…

実行結果

関連構文

UPDATE … FROMについてよくある質問

UPDATE … FROMは標準SQLですか?
標準SQLではなく、PostgreSQLの拡張です。公式ドキュメントも、FROMとRETURNINGはPostgreSQLの拡張だと説明しています。標準SQLに沿った書き方にしたいときは、相関サブクエリで書きます。
MySQLのUPDATE JOINと同じように書けますか?
MySQLの「UPDATE 表 JOIN 別の表 ON 条件 SET …」は、PostgreSQLではそのままでは動きません。別の表をFROMに書き、結合条件をWHEREに書き換えます。SQL ServerのようにFROMで更新先を再掲する書き方も、PostgreSQLでは自己結合になるので使えません。
別の表に一致しない行はどうなりますか?
FROMの結合に一致しない行は更新対象にならず、そのまま残ります。NULLに書き換わるのは、SETの右辺をサブクエリで書き、WHEREで絞らなかったときです。
MERGEとの違いは何ですか?
UPDATE … FROMは「一致した行を更新する」だけです。一致した行は更新し、一致しない行は追加する、というように1文で分けて処理したいときは、PostgreSQL 15以降で使えるMERGEが向いています。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。