mysql 批量删除/更新数据报错 you can‘t specify target / every derrived table must / you are using safe update

执行一段SQL，批量删除冗余数据时报错：You can't specify target table 'A' for update in FROM clause

delete from A where id in (select id from A group by id having count(id)>1);

 改为：

delete from A where id in (select id from (select id from A group by id having count(id)>1));

原因：查询优化器会为临时表做合并优化，导致报错。第二个查询把临时表又套了一层，没有指定优化，因此优化器被强制先执行子查询。

官方解释：MYSQL Derived Table Merge Optimization

改成如上SQL之后会报：Every derived table must have its own alias

给临时表加一个别名即可：

delete from A where id in (select id from (select id from A group by id having count(id)>1) a);

如果是在MySQL WorkBench上操作还会遇到安全模式的问题：You are using safe update mode and you tried to update a table without a WHERE ...

修改安全模式为false即可，重启失效：

set sql_safe_updates = 0;

更新/删除成功。