mysql - 如何删除表中所有重复记录？

Question

我正在寻找一种方法来删除表中的所有重复记录。我有一个查询，但没有成功:

delete from lista
where id in
(
    select id
    from lista
    group by tytul_pl
    having count(tytul_pl) > 1
);

服务器响应:

Error Code: 1093. You can't specify target table 'lista' for update in FROM clause

此查询:

select id
from lista
group by tytul_pl
having count(tytul_pl) > 1

运行良好。

哪里出错了？

Answer 1

这将有助于保留一条记录并删除所有其他重复项:

DELETE t1 
FROM lista t1
JOIN lista t2 
 ON t1.id > t2.id 
AND t1.tytul_pl = t2.tytul_pl