参考文章:
学生表如下:
- 执行语句:
delete from students where id not in (select Min(id) as id from students group by name,sex,score,subject);
- 报错:
ERROR 1093 (HY000): You can't specify target table 'students' for update in FROM clause
原因:不能先select出同一表中的某些值,再delete这个表(在同一语句中)
- 修改如下:
delete from students where id not in (select students.id from (select Min(id) as id from students group by name,sex,score,subject) students );
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和oracle不会出现此问题。