mysql - 您不能在 FROM 子句中指定目标表“人”进行更新

Question

我需要删除重复的电子邮件。

在 SQLite 中，这是正确的 sql 查询：

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

但是当我尝试在 MySQL 上使用相同的查询时，我得到了错误：

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

PS我也尝试通过内部连接，但它没有帮助。

标签： mysql

Answer 1

您需要这样的表的自联接：

delete p1 
from person p1 inner join person p2
on p2.email = p1.email and p2.id < p1.id

或者：

delete p
from person p 
inner join (
  select email, min(id) id
  from person
  group by email
) t on t.email = p.email and t.id < p.id