あいさつ

むかし業務で必要になった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を見てしまえば「なんだ単純じゃないか」という話ですが、どうしたらいいかちょっと悩んだ記憶があるので記事にしました。

それでは。