「テーブルを結合して、レビューがある商品だけ更新したい」
本と本を借りている人(1対１)、商品と商品へのレビュー(1対多)みたいにテーブル結合を使ったUPDATE文を実行したいとします。
こういう時、posgresqlではJOINを使うとエラーが出る。
公式リファレンス
解決する方法として
・FROMで結合先テーブルを指定する方法
・サブクエリを使う方法
があるので、それぞれ紹介します。
1対1編 FROM句を使う
FROM句で結合先テーブルを指定、
結合後のレコードに対してUPDATEします。
UPDATE products SET popular_flag = true
FROM reviews -- 実質 INNER JOIN と同じ
WHERE products.id = reviews.product_id; -- ONと同じ
これにより、reviewsが紐づくproductsのレコード(レビューがある商品)だけを更新できます。
結合後にさらに条件を追加する
AND句で条件を追加することで、結合後のレコードをさらに絞り込んでUPDATEできます。
UPDATE products SET popular_flag = true
FROM reviews
WHERE products.id = reviews.product_id
-- 価格1万円以上で削除されてない商品のみUPDATE
AND price >= 10000
AND products.deleted_at IS NULL;
注意点
互換性
FROM句およびRETURNING句はPostgreSQLの拡張です。
ということなので、他のRDBでは使えません。
注釈
FROM句が存在する場合、基本的に、対象テーブルとfrom_listで指定されたテーブルが結合され、この結合の出力行が対象テーブルの更新操作の結果となります。 FROM句を使用する場合、更新対象テーブルの1行に対して、結合結果が複数行にならないように注意してください。 言い換えると、対象テーブルの個々の行は、他テーブルの複数の行と結合すべきではありません。 結合結果が複数行になった場合、対象行の更新には結合結果のいずれか1行のみが使用されますが、どの行が使用されるかは簡単には予測できません。
長ったらしいですね。具体例で考えると分かりやすいです。
- 本と本の貸し出し状況のテーブルを結合して、UPDATE
本と本を借りれる人は1対1なので、結合後にbooks rentalsどちらを更新してもOK
- 商品と商品レビューのテーブルを結合して、UPDATE
商品(1対多の1のほう)を更新 => OK
レビュー(1対多の多のほう)を更新 => NG 更新漏れが起こる
結合する時に結びつく相手が複数ある場合、複数の中から1つの結び付きだけが選択され、更新されます。
このような不定性の問題があるため、他テーブルの参照は副問い合わせ内のみに留めておいた方がより安全です（ただし、結合よりも可読性や実行速度は低下します）。
ということで、続いてはサブクエリを使ったUPDATEをやってみます。
1対多の「多」をUPDATE編 サブクエリを使う
FROM句は使わず、WHEREの値でサブクエリを使います。
商品に対するレビューの公開フラグを一律で更新するならば、
UPDATE products SET popular_flag = true
WHERE products.id =
(SELECT product_id FROM reviews);
となります。
サブクエリを書き慣れている人ならばこちらの方がいいかもしれませんね。
こちらについても、AND句で条件を追加することができるようです。