mysql delete in 错误

错误

You can’t specify target table ‘movies2’ for update in FROM clause

描述: 如果子查询的 from 子句和更新、删除对象使用同一张表,会出现上述错误。

解决方法: 通过给 from 子句中的结果集起别名。

如,原本要删除的子查询为

1
2
3
delete from movies2 where id in (
select id from movies2 where title like "%集%"
)

修改为

1
2
3
4
5
delete from movies2 where id in (
select nobige.id from (
select id from movies2 where title like "%集%"
) as nobige
)

同理,也可以临时创建一个查询结果表在通过此表进行删除操作。

1
2
3
4
5
6
7
-- 创建临时表
create into temp as
select id from movies2 where title like "%集%";

delete from movies2 where id in (
select * from temp
)

参考