mysql delete in 结果集,MySQL delete SQL 常见错误

where 条件中表名使用了别名

delete from student m where m.id = 1;

错误：

[Err] 1064 - You have an error in your SQL syntax;

分析：

delete from table 这样的 SQL 中 table 不能使用别名。

解决方法：

delete from student where id = 1;

where 条件里有子查询

delete from student where id = (select max(id) from student);

错误：

[Err] 1093 - You can't specify target table 'student' for update in FROM clause

分析：

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

解决方法：

通过给 from 子句中的结果集起别名。

delete from student where id = (select n.max_id from (select max(id) as max_id from student) as n);

in 条件里有子查询(原因同上)

delete from student where id in (select id from student where id > 30);

错误：

[Err] 1093 - You can't specify target table 'student' for update in FROM clause

解决方法：

delete from student where id in (select n.id from (select id from student where id > 30) as n);

标签：SQL,student,MySQL,table,where,id,select,delete

来源： https://www.cnblogs.com/dwarcheng/p/14465519.html