MySQLのDELETE、UPDATE文で更新対象のテーブルを含むサブクエリを実行する方法

2023-01-06

MySQLのDELETE、UPDATE文で更新対象のテーブルを含むサブクエリを直接実行することができない

例えば以下のようなテーブルがあるとして、

shell
1mysql> select * from members;
2+------+--------+
3| id | name |
4+------+--------+
5| 1 | Taro |
6| 2 | Hanako |
7| 3 | Takao |
8| 4 | Yasuko |
9| 5 | Kenji |
10+------+--------+
115 rows in set (0.00 sec)
12
13mysql> select * from member_detail;
14+------+-----------+-------+------------+
15| id | member_id | sex | birthday |
16+------+-----------+-------+------------+
17| 1 | 1 | mem | 1989-05-16 |
18| 2 | 2 | women | 1980-04-30 |
19| 3 | 3 | mem | 2002-09-04 |
20| 4 | 4 | women | 1979-03-21 |
21| 5 | 5 | mem | 1960-05-22 |
22+------+-----------+-------+------------+
235 rows in set (0.00 sec)

以下のDelete文はエラーとなります。

sql
1delete from members where id in (
2 select member_id
3 from member_detail md
4 inner join members m on m.id = md.member_id
5 where birthday > '1980-01-01'
6);
7ERROR 1093 (HY000): You can't specify target table 'members' for update in FROM clause

FROM 句で更新対象のテーブル 'members' を指定することはできません

この場合、サブクエリ内で1段階層を深くして削除対象のidを抽出するようにすると実行が可能になります。

sql
1delete from members where id in (
2 select * from (
3 select member_id
4 from member_detail md
5 inner join members m on m.id = md.member_id
6 where birthday > '1980-01-01'
7 ) sub
8);

Query OK, 3 rows affected (0.01 sec)

また、直接From句で参照しない場合は問題なく実行できます。

sql
1delete from members where id in (
2 select member_id
3 from member_detail
4 where birthday > '1980-01-01'
5);

Query OK, 3 rows affected (0.01 sec)

以下は検証環境構築のメモです。

検証環境構築メモ

shell
1docker run --name test_mysql -e MYSQL_ROOT_PASSWORD=password -d mysql:8.0.31
2docker exec -it test_mysql bash

MySQLに接続する。

shell
1mysql -u root -p

データベース作成、データ投入。

sql
1create database test_db;
2use test_db
3create table members (id int, name varchar(255));
4insert into members values (1, 'Taro'),(2, 'Hanako'),(3, 'Takao'),(4, 'Yasuko'),(5, 'Kenji');
5create table member_detail (id int, member_id int, sex varchar(255), birthday date);
6insert into member_detail values (1, 1, 'men', '1989-05-16'),(2, 2, 'women', '1980-04-30'),(3, 3, 'men', '2002-09-04'),(4, 4, 'women', '1979-03-21'),(5, 5, 'men', '1960-05-22');

Select句は直接参照しても問題無く実行できる

sql
1select * from members where id in (
2 select member_id
3 from member_detail md
4 inner join members m on m.id = md.member_id
5 where birthday > '1980-01-01'
6);

Tech Blog

avatar

ソフトウェアエンジニア。1989年生まれ大阪府岸和田市在住のフリーランス。PHP、バックエンド開発が得意。テニス、フットサル、だんじり、ケツメイシ、競馬、プログラミングが好き!最近はWebフロントエンド沼にハマってます!

Copyright © 2023. Junpeko5's Tech Blog