あいさつ
むかし業務で必要になった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の値と一致するよう更新する必要が出たとしましょう。
この作業を一括で行いたいときどうしたらよいでしょう。
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の場合は
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を見てしまえば「なんだ単純じゃないか」という話ですが、どうしたらいいかちょっと悩んだ記憶があるので記事にしました。
それでは。