MySQL之You can't specify target table for update in FROM clause错误解决

MySQL 根据子句进行条件更新或删除时,报如下错误:You can’t specify target table for update in FROM clause

错误意思是指:在同一语句中,update 和 select 子句不能是同一个表。

解决方式:需要把 SELECT 条件子句的结果集转为中间临时表,此临时表会依赖于 SELECT 条件子句驱动表。

此问题通常出现在 UPDATE 和 DELETE 需要根据条件 SELECT 子句来操作的场景。

UPDATE 示例:

1
2
3
UPDATE user_info SET `status` = 0 WHERE id IN (
SELECT max(id) FROM user_info GROUP BY `code` HAVING count(`code`) > 1
)

会报错:You can’t specify target table ‘user_info’ for update in FROM clause

解决示例:

1
2
3
4
5
6
7
8
UPDATE user_info SET `status` = 0 WHERE id IN (

SELECT t.id FROM (

SELECT max(id) as id FROM user_info GROUP BY `code` HAVING count(`code`) > 1

) AS t
)

EXPLAIN 分析:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> EXPLAIN
UPDATE user_info SET `status` = 0 WHERE id IN (

SELECT t.id FROM (

SELECT max(id) as id FROM user_info GROUP BY `code` HAVING count(`code`) > 1

) AS t
);
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+-----------------+
| 1 | UPDATE | user_info | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | NULL | index_subquery | <auto_key0> | <auto_key0> | 5 | func | 2 | 100.00 | Using index |
| 3 | DERIVED | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+-----------------+
3 rows in set (0.04 sec)

DELETE 操作报此错也是同样方式即可解决。

MySQL之You can't specify target table for update in FROM clause错误解决

http://blog.gxitsky.com/2022/07/08/MySQL-Error-cant-specify-table-in-from-clause/

作者

光星

发布于

2022-07-08

更新于

2022-11-04

许可协议

评论