不能对同一张表先查询后更新的解决方案

在刷LeetCode时，遇到一道删除重复邮箱的题，一开始我的sql语句是这样的

delete from person
where id in
(select max(id) from person
group by email
having count(0) > 1)

 

但是提示错误：You can't specify target table 'person' for update in FROM clause

意思是，不能对一张表先查询后更新

那只能是设置一张中间表，如下

delete from person
where id in
(select id from
(select max(id) as id from person
group by email
having count(0) > 1) as tempt
)

下面这个答案也没通过，但那属于业务逻辑的问题，语法是正确的。

转载于:https://www.cnblogs.com/dubhlinn/p/10574503.html