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 示例：

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

解决示例：

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 分析：

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 操作报此错也是同样方式即可解决。