別の表の値を使って一括更新する場合は、更新文を書く前に、対象の各行がどの入力行と結び付くかを確認します。検索結果が見えることと、意図した行だけを更新できることは別です。
ここではPostgreSQLのUPDATE ... FROMを例に説明します。DB製品によって構文や条件が異なります。コードは未実行の説明例で、そのまま本番へ適用する手順ではありません。
更新対象ごとに参照する行を一つにする
PostgreSQLのUPDATE仕様は、FROMを使う場合、更新対象の一行に対して結合結果が最大一行になるよう求めています。複数行が一致すると、どの行の値を使うかを容易に予測できません。
仮に商品価格表へ更新用データを適用するなら、更新用データの商品IDが一意かを確認します。同じ商品に二つの価格があれば、先に業務上どちらを採用するか決める必要があります。
更新前のSELECTで対象と変更値を確認する
次は架空のproductsとprice_updatesを対応させる確認例です。商品ID、現在値、予定値を並べます。
SELECT p.product_id,
p.price AS current_price,
u.new_price
FROM products AS p
JOIN price_updates AS u
ON u.product_id = p.product_id;
件数だけでなく、対象外にしたい商品が含まれていないか、予定値のNULLや単位が妥当かを確認します。更新用表の一意性は、たとえば商品IDごとの件数を調べて点検します。
SELECT product_id, COUNT(*)
FROM price_updates
GROUP BY product_id
HAVING COUNT(*) > 1;
この確認が空でも、業務条件の正しさまで保証するものではありません。対象期間や価格の適用条件を別に照合します。
確認した対応条件をUPDATEへ持たせる
対応が一意で、必要な制約を確認した場合の説明例です。
UPDATE products AS p
SET price = u.new_price
FROM price_updates AS u
WHERE u.product_id = p.product_id
RETURNING p.product_id, p.price;
RETURNINGで更新後の対象を確認できます。PostgreSQLの更新件数には、一致したが値が変わらなかった行も含まれるため、「値が変化した件数」と同一視しません。UPDATEの出力
事前確認と実行の間の変更を考える
SELECTした後に別の処理が対象表を更新する可能性があります。事前確認の結果だけを、後の実行時点の保証として扱わないことが必要です。
実運用では、更新時間帯、トランザクション、同時実行の制御、必要な承認、復元の準備を、対象DBと業務条件に合わせて決めます。確認用SELECTとUPDATEの条件を揃えることは、その一部です。
取り消し可能な範囲と完了条件を決める
更新後は予定した対象・値と結果を照合し、確定する条件を判断します。ロールバックの範囲も参照し、確定済み変更や外部への影響を単純に戻せると考えないようにします。
一意な対応、期待する変更、実行時の条件、結果の照合を一組にすることで、JOINを使う更新の確認漏れを減らせます。