はじめに
SQLでUPDATE文を書くときに、こんなこと出来ないかな？という場面で、ちょっと知っていると役に立つ書き方あれこれです。
SQLに関わらず、ちょっと知っているというのは案外大事で、あれで出来るかも？とか、もしかしたらこれも出来るのでは？といった応用もききやすくなりますね。
サンプルの確認環境はMySQL 5.7です。実行環境により書き方が異なる場合があるのでご注意ください。
1．UPDATE文の基本
基本となるUPDATE文の書き方は下記のような感じですね。
[条件]には更新する行を特定するための条件式を書く。
UPDATE
テーブル名
SET
列名1 = 値1
,列名2 = 値2
[,･･･]
WHERE
[条件];
2．テーブルの値を別テーブルの値でUPDATEする(其の壱)
【サンプルデータ】
下記の例では、nameテーブルのattr_nameにattributeテーブルのattr_nameをセットする。
※ RDBならそもそもnameテーブルにattr_nameを持つ必要は無いですがサンプルとして...。
■nameテーブル
| id | attr_id | name | attr_name |
|---|---|---|---|
| 1 | 1 | ヒバニー | NULL |
| 2 | 1 | ラビフット | NULL |
| 3 | 1 | エースバーン | NULL |
| 4 | 2 | メッソン | NULL |
| 5 | 2 | ジメレオン | NULL |
| 6 | 2 | インテレオン | NULL |
■attributeテーブル
| attr_id | attr_name |
|---|---|
| 1 | ほのお |
| 2 | みず |
■UPDATE文
UPDATE
テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.列名X = テーブル名2.列名X
SET
テーブル名1.列名1 = テーブル名2.列名2;
下記でも同じ
UPDATE
テーブル名1
,テーブル名2
SET
テーブル名1.列名1 = テーブル名2.列名2
WHERE
テーブル名1.列名X = テーブル名2.列名X;
■サンプルデータを使ったSQL例
UPDATE
name A
,attribute B
SET
A.attr_name = B.attr_name
WHERE
A.attr_id = B.attr_id;
上記SQLサンプルではnameテーブルとattributeテーブルに、それぞれ別名A、Bを付けています。
ポイントは、A.attr_nameを更新するB.attr_nameが対象レコードに対して一意に決まるようWHEREの条件を定義することです。
■結果
nameテーブル
| id | attr_id | name | attr_name |
|---|---|---|---|
| 1 | 1 | ヒバニー | ほのお |
| 2 | 1 | ラビフット | ほのお |
| 3 | 1 | エースバーン | ほのお |
| 4 | 2 | メッソン | みず |
| 5 | 2 | ジメレオン | みず |
| 6 | 2 | インテレオン | みず |
3．テーブルの値を別テーブルの値でUPDATEする(其の弐)
2．と同じことを副問い合わせの書き方で。
■UPDATE文
UPDATE
テーブル名1
SET
テーブル名1.列名1 = (
SELECT
テーブル名2.列名2
WHERE
テーブル名1.列名X = テーブル名2.列名X
);
■サンプルデータを使ったSQL例
UPDATE
name A
SET
A.attr_name = (
SELECT
B.attr_name
FROM
attribute B
WHERE
A.attr_id = B.attr_id
);
何度も書きますが、更新する際に大事なのは、A.attr_nameを更新するB.attr_nameが対象レコードに対して一意に決まるようWHEREの条件を定義することです。
結果はもちろん上の2と同じです。
4．テーブルの更新条件を副問い合わせで取ってくる
副問い合わせで取得することでSELECTした結果を退避しておいてセットし直すということをしなくて済みますね。
【サンプルデータ】
下記の例ではnameテーブルのattr_nameにattribute2テーブルのattr_nameが「ほのお」のみセットする。
■nameテーブル
| id | name | attr_name |
|---|---|---|
| 1 | ヒバニー | NULL |
| 2 | ラビフット | NULL |
| 3 | エースバーン | NULL |
| 4 | メッソン | NULL |
| 5 | ジメレオン | NULL |
| 6 | インテレオン | NULL |
■attribute2テーブル
| id | attr_name |
|----+-----------|
| 1 | ほのお |
| 2 | ほのお |
| 3 | ほのお |
| 4 | みず |
| 5 | みず |
| 6 | みず |
■サンプルデータを使ったSQL例
今回はサンプルデータのSQLのみ書きます。
UPDATE
name A
SET
A.attr_name = 'ほのお'
WHERE
A.id IN
(
SELECT
B.id
FROM
attribute2 B
WHERE
B.attr_name = 'ほのお'
);
今回用意したサンプルデータがあまり良くないですが、あくまでこんな書き方が出来ますという例です。
もちろん、このサンプル例は上の2や3の書き方でも出来ます。
例えば下記のようなケースのフラグ立てなどにも使えます。
- ユーザーテーブルに対し売上テーブルから、ある期間において売上が〇円以上の人にフラグを立てる
- 商品テーブルに対し売上テーブルから、〇月の売上トップ10の商品にフラグを立てる
■結果
| id | name | attr_name |
|----+--------------+-----------|
| 1 | ヒバニー | ほのお |
| 2 | ラビフット | ほのお |
| 3 | エースバーン | ほのお |
| 4 | メッソン | NULL |
| 5 | ジメレオン | NULL |
| 6 | インテレオン | NULL |
5．その他（ORDER BY句 や LIMIT句）
ORDER BY句やLIMIT句を使ってみる。（あまり使ったことないですが...）
例えば先ほどのnameテーブルの結果のid（4,5,6)のattr_nameに「みず」をセットする。
| id | name | attr_name |
|----+--------------+-----------|
| 1 | ヒバニー | ほのお |
| 2 | ラビフット | ほのお |
| 3 | エースバーン | ほのお |
| 4 | メッソン | NULL |
| 5 | ジメレオン | NULL |
| 6 | インテレオン | NULL |
UPDATE
name A
SET
attr_name = 'みず'
ORDER BY
id DESC
LIMIT 3;
ORDER BY句は並び順（昇順、降順）を指定し、LIMIT句は件数を指定します。
■結果
| id | name | attr_name |
|----+--------------+-----------|
| 1 | ヒバニー | ほのお |
| 2 | ラビフット | ほのお |
| 3 | エースバーン | ほのお |
| 4 | メッソン | みず |
| 5 | ジメレオン | みず |
| 6 | インテレオン | みず |
おまけ（テーブルのコピー）
UPDATE文を流す際に万が一に備えテーブルのバックアップを取っておきたい場合に。
CREATE TABLE
name_back
AS
SELECT
*
FROM
name;
INDEX等はコピーされないのでご注意ください。
おわり。