You can't specify target table for update in FROM clause

Error Code: 1093. You can't specify target table for update in FROM clause.

对一张表执行查询同时更新/删除时会出现异常。

解决办法:将查询结果放入临时表。

问题示例:

DELETE FROM `t_a`

WHERE id in( 

SELECT a.id FROM  `t_a` as a where a.value is null

);

说明:示例为简化后的查询条件,实际中对单表有条件的删除不会这么写。

解决方案:在查询外面再加一个查询形成临时表。

DELETE FROM `t_a`

WHERE id in( 

SELECT t.id FROM

(SELECT a.id FROM  `t_a` as a where a.value is null) as t

);