Joinの結果を条件にテーブルの値を更新する

あいさつ

むかし業務で必要になったSQLの小技？を紹介しようと思います。（初投稿です）

本題

サンプルケース

• table1

id	name	hoge
1	taro	3
2	john	6
3	kevin	10
4	saya	1

• table2

id	piyo	grade
1	5	2
2	9	3
3	8	5
4	7	2

以上のような二つのテーブルがあり、なんらかの事情でgradeが2の人のみ、table1のhogeの値をtable2のpiyoの値と一致するよう更新する必要が出たとしましょう。
この作業を一括で行いたいときどうしたらよいでしょう。

oracle

update
    (SELECT a.hoge hoge,
         b.piyo piyo,
         b.grade grade
    FROM table1 a
    INNER JOIN table2 b
        ON a.id = b.id) SET hoge = piyo
WHERE grade = 2;

上記SQL実行後table1は以下のようになります。

id	name	hoge
1	taro	5
2	john	6
3	kevin	10
4	saya	7

結合したときgradeが2だったtaroとsayaの2レコードのhoge値が、table2のpiyo値で更新されます。
ポイントは結合しているサブクエリ内のselect句で各カラムに別名をつけていることです。
なぜならサブクエリの外側で必要になるからです。
ちなみに別名の付け方を見ればお気づきと思いますが、これはoracleでの書き方です。
oracleではupdate句の中でjoinが使えません。(できるバージョンもあるかもしれません、、)

SQL serverの場合は

SQLserver

UPDATE table1 SET a.hoge = b.piyo
FROM table1 AS a
INNER JOIN table2 AS b
    ON a.id = b.id
WHERE b.grade = 2;

こんな感じで割と素直に書けるようです。
postgresだと、また違った書き方だったような気がするのですが、ぱっと出てこないので割愛します。

余談＆思い出話

こういった作業が必要になったのが1年以上昔のはなしなので、実際どういう場面だったか正確には覚えていないのですが、軽いネタにはちょうどいいかと初投稿してみました。
当時はVB.NETとoracleを使った市役所向けパッケージをやっていて、レガシーな環境にうんざりしていましたね。
とはいえこのような学びはちょこちょこあったのですが。わたしは最近SQLを直接書く機会があまりないですね。

ちなみにサンプルケースのテーブルがクソってのは置いといてください。
このようなSQLが必要になる場面はテーブル設計がちゃんとしていればあまりなさそうですよね。
SQLを見てしまえば「なんだ単純じゃないか」という話ですが、どうしたらいいかちょっと悩んだ記憶があるので記事にしました。

それでは。