はじめに

誤ったUPDATE文を実行し自動commitしてしまったことがありました。

そのため、UPDATE文の更新対象を簡単に確認したいと考えました。

今回検討した方法としては副問い合わせ内のSELECT文で確認後、全体のUPDATE文を実行します。

概要

1. 対象データの確認
2. 更新SQLの作成
3. メリットとデメリット

対象データの確認

年齢が低い子供の点数にボーナスを加える状況を考えます。

点数テーブルscoretableと年齢テーブルagetableを無理矢理分けたテーブルがあるとします。

どちらもid項目が主キーとなっています。

今回の更新処理に複数テーブルを使用したかったため、テーブル構成については気にしないでください。

点数テーブルscoretable

id	name	score
1	aaa	90
2	bbb	40
3	ccc	70
4	ddd	30

年齢テーブルagetable

id	name	age
1	aaa	33
2	bbb	7
3	ccc	35
4	ddd	6

年齢が10歳未満の人の点数を2倍に更新する場合を考えます。

更新後のscoreの値をscore_newと定義すると、確認用SQLは次のようにします。

SELECT st.id, st.name, at.age, st.score, st.score * 2 AS score_new 
FROM scoretable st
  LEFT JOIN agetable at ON st.id = at.id
WHERE at.age < 10

実行結果

id	name	age	score	score_new
2	bbb	7	40	80
4	ddd	6	30	60

10歳未満の2名の点数scoreに対して、更新したい点数score_newが2倍の数値であることを確認できます。

この主キーidによる紐づけを行うことでUPDATEを行いたいと考えます。

更新SQLの作成

PostgreSQLのUPDATE文ではFROM句で別テーブルを参照できます。

ただし、UPDATE文ではJOIN句は使用できないため、WHERE句でテーブル結合できます。

対象データ確認SQLを副問い合わせとして参照し、キーで結合すれば一意に定まり更新できます。

UPDATE scoretable
SET score = st_new.score_new
FROM (
-- 確認用SQL
SELECT st.id, st.name, at.age, st.score, st.score * 2 AS score_new 
FROM scoretable st
  LEFT JOIN agetable at ON st.id = at.id
WHERE at.age < 10
) st_new
-- 主キーによる紐づけ
WHERE scoretable.id = st_new.id

実行結果

id	name	score
1	aaa	90
2	bbb	80
3	ccc	70
4	ddd	60

副問い合わせ内のSELECT文を実行することで更新対象データを確認できます。

問題なければ全体のUPDATE文を実行することでミスを減らすことが出来ると考えます。

メリットとデメリット

データを確認をしながら行うリカバリ時やパッチ適用時などで慎重になれるかと思います。

他に利用者がいないのであれば、トランザクション中に更新、内容確認をするのが一番だと思います。

メリット

• UPDATE文の実行結果となりえるデータを部分実行で確認できる。
• 副問い合わせのキーで更新するため、複数のテーブル結合を用いた計算結果でも反映することが出来る。

デメリット

• 実行速度が低下する。
• 確認用SQL実行と間違えてUPDATE文を実行してしまう可能性は残っている。