问题:
错误写法:
delete from Person where Id not in (select Min(Id) from Person Group by Email)
错误原因:
在MYSQL中,不能先Select一个表的记录,再按此条件Update和Delete同一个表的记录,否则会出错:You can’t specify target table ‘xxx’ for update in FROM clause.
解决办法:
用嵌套Select——将Select得到的查询结果作为中间表,再Select一遍中间表作为结果集,即可规避错误。
正确写法
delete from Person where Id not in (select * from (select Min(Id) from Person Group by Email) as t)
值得注意的就是中间表必须指定别名