MySQL 中 delete where in 语句的子查询限制
-
场景一
delete from table1 where id = (select max(id) from table1 ); [Err] 1093 - You can’t specify target table table1 ’ for update in FROM clause
描述: 如果子查询的 from 子句和更新、删除对象使用同一张表，会出现上述错误。 解决方法: 通过给 from 子句中的结果集起别名。
delete from table1 where id = (select n.max_id from (select max(id) as max_id from table1 ) as n);
上述情况对于 in 子句也适用，in后面的子查询不能带where条件，带where条件的必须使用别名
delete from table1 where id in (select id from table1 where id > 30); [Err] 1093 - You can’t specify target table table1 ’ for update in FROM clause
解决方法同上:
delete from table1 where id in (select from (select id from table1 where id > 30) as n);
-
场景二
delete from table1 m where = 1; [Err] 1064 - You have an error in your SQL syntax;
描述: delete from table 这样的句子中 table 不能使用别名。 解决方法：去掉别名:
delete from table1 where id = 1;
