Search code examples
mysqlsqlwhere-clausesql-delete

How do I delete all repeating records in a table?

I'm looking for a way to delete all repeating records in table. I have a query, but it didn't work:

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

Server response:

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

This query:

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

is working well.

Where is the error?

Solution

  • I think what you want then is this

    DELETE lista FROM lista 
LEFT OUTER JOIN (
      SELECT MIN(ID) AS minID 
      FROM lista 
      GROUP BY tytul_pl) AS keepRowTable ON lista.ID = keepRowTable.minID
WHERE keepRowTable.minID IS NULL

    You can try it out here