MySQL 中 delete ,update语句的子查询限制

场景一

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);

场景二

delete from student m where m.id = 1; [Err] 1064 - You have an error in your SQL syntax;

描述: delete from table 这样的句子中 table 不能使用别名。

解决方法：去掉别名:

delete from student where id = 1; Whatever is worth doing is worth doing well. mysql的功能问题 mysql不支持对同一个表查询后做修改(update、delete)操作 update score set 姓名='王五' where 总分=(select score from (select min(总分) as score from score) as a); 制定个新表名就可以了