MySQLのUPDATE文の中でSELECTステートメントを使う方法

MySQLでUPDATEコマンドを実行する際に、同じテーブルを参照するSELECTステートメントを組み合わせたいケースはよくあります。しかし、UPDATE文の中で直接サブクエリとして対象テーブルを指定するとエラーになるため、工夫が必要です。

解決策：サブクエリとWHERE句を組み合わせる

この問題を回避するには、WHERE句とともにサブクエリを使用し、さらにそのサブクエリを別のSELECT文でラップ（派生テーブル化）します。これにより、MySQLは一時的なテーブルとして扱うため、更新中のテーブルへの参照エラーを防ぐことができます。

実際の手順を順番に見ていきましょう。

1. テーブルを作成する

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> Id int,
    -> Name varchar(20)
    -> );
Query OK, 0 rows affected (0.82 sec)

2. レコードを挿入する

INSERTコマンドを使って、いくつかのレコードを追加します。

mysql> insert into DemoTable values(100,'Chris');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values(250,'David');
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable values(150,'Mike');
Query OK, 1 row affected (0.12 sec)

3. テーブルの内容を確認する

SELECTステートメントですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+------+-------+
| Id   | Name  |
+------+-------+
| 100  | Chris |
| 250  | David |
| 150  | Mike  |
+------+-------+
3 rows in set (0.00 sec)

4. UPDATE文の中でSELECTを使用する

ここがポイントです。以下のように、サブクエリを別のSELECT文で囲むことで、UPDATE処理中にSELECTステートメントを利用できます。この例では、最大のIdを持つ行のNameを「Robert」に更新しています。

mysql> update DemoTable
    -> set Name='Robert'
    -> where Id in
    -> (
    -> select *from ( select max(Id) from DemoTable ) tbl1
    -> );
Query OK, 1 row affected (0.27 sec)
Rows matched: 1 Changed: 1 Warnings: 0

注目すべきは select *from ( select max(Id) from DemoTable ) tbl1 の部分です。内側のサブクエリの結果に対して「tbl1」というエイリアスを付けることで、派生テーブルとして認識され、「You can't specify target table for update in FROM clause」というエラーを回避できます。

5. 更新結果を確認する

もう一度テーブルの内容を確認しましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+------+--------+
| Id   | Name   |
+------+--------+
| 100  | Chris  |
| 250  | Robert |
| 150  | Mike   |
+------+--------+
3 rows in set (0.00 sec)

Id「250」のNameが「David」から「Robert」に正しく更新されていることがわかります。

まとめ

MySQLのUPDATE文内で同じテーブルをSELECTしたい場合は、サブクエリをさらに別のSELECT文でラップし、エイリアスを付けることが重要です。このテクニックを覚えておけば、集計結果に基づいた更新なども柔軟に行えるようになります。