MySql 如何根据该表中的分组更新表？

Question

当我运行下面的查询时，我得到了

error #1093 - You can't specify target table 'x2' for update in FROM clause

update contacts 
set duplicates = 1
where email in ( select email from contacts 
    group by email
    having count(*) > 1)

如有任何帮助，我们将不胜感激。

Answer 1

您可以执行 JOIN 以查找具有相同电子邮件和不同主键的其他行。

例如，假设您的主键名为 id:

UPDATE contacts AS c1
JOIN contacts AS c2 ON (c1.email = c2.email AND c1.id <> c2.id)
SET c1.duplicates = 1;