mysql - 无法在 FROM 子句中指定更新目标表 'table_name'

Question

这个问题已经有答案了:

MySQL Error 1093 - Can't specify target table for update in FROM clause (16 个回答)

已关闭 5 年前。

我开始更深入地阅读 MySql 并尝试在 description 上解决此任务。根据描述需要从表中删除重复项，但我的查询不起作用。在 sqllite 中工作正常。原因是什么？

DELETE
FROM
    Person
WHERE
    Email IN(
    SELECT
        Email
    FROM
        Person
    GROUP BY
        Email
    HAVING
        COUNT(Email) > 1
);

错误

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

Answer 1

您可以使用自连接来删除重复项

delete a
from Person a
left join Person b on a.Email = b.Email
                 and a.id > b.id
where b.Email is not null

Demo

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+