题目链接:力扣
报错代码一:
DELETE from Person where (id) not in( select min(a.id) as id from Person as a group by a.email )
报错提示:You can't specify target table '表名' for update in FROM clause
原因:不能先select出同一表中的某些值,再update这个表(在同一语句中)报错代码二:
DELETE from Person where (id) not in( SELECT id from (select min(a.id) as id from Person as a group by a.email) )
报错提示:Every derived table must have its own alias
翻译:每个派生表都必须有自己的别名
原因:在sql语句中建立的子表没有别名
正确代码:
DELETE from Person where (id) not in( SELECT id from (select min(a.id) as id from Person as a group by a.email) as t )
补充:因为mysql要求不能对同一个表进行查询的同时发生修改(报错一),所以需要通过重复查询生成临时子表,但临时子表必须携带别名(报错二)。