MySQL出现You can‘t specify target table for update in FROM clause解决办法

题目力扣P196题

写成

delete

from Person p1

where p1.id not in(

    select min(p2.id)

from Person p2

where p1.email=p2.email

Group by p2.email

)

会报错：You can't specify target table for update in FROM clause

解决方法：在内部搜索中再用select搜索一次即可。

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。

delete

from Person p1

where p1.id not in(

select id

from(

select min(p2.id) as id

from Person p2

where p1.email=p2.email

Group by p2.email

)t

)