You can't specify target table 'Person' for update in FROM clause

最新推荐文章于 2024-08-15 09:31:54 发布

涂有

最新推荐文章于 2024-08-15 09:31:54 发布

阅读量4.2k

点赞数 5

分类专栏：数据库文章标签： mysql

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/ty497122758/article/details/90274377

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

delete from Person 
where id not in (
    select min(Id) id from Person group by Email having count(Email) > 1
) or Email not in (
    select Email from Person group by Email having count(Email) = 1
)

执行这条语句时会报错：You can't specify target table 'Person' for update in FROM clause

这是因为MySQL不允许同时查询和删除一张表，我们可以通过子查询的方式包装一下即可避免这个报错

delete from Person 
where id not in (
    select id from (select min(Id) id from Person group by Email having count(Email) > 1) a
) and Email not in (
    select Email from (select Email from Person group by Email having count(Email) = 1) b
)

这样就不会报错了