how to delete rows where a single column value is duplicated

Question

I need to remove all rows having duplicated title column. Something like:

delete from clients where title is duplicated

For example, if five rows have the same title column - four of them shold be deleted.

How to do this?

Answer 1

If you have a unique column like id or somedate:

delete c 
from clients c inner join clients cc
on cc.title = c.title and cc.id < c.id

This code will keep the row with the minimum id among the duplicates.
See the demo.

Answer 2

If you want to keep the one with lowest ID:

DELETE cl1 FROM clients cl1, clients cl2 WHERE cl1.title = cl2.title AND cl1.id > cl2.id

If you want to keep the one with highest ID:

DELETE cl1 FROM clients cl1, clients cl2 WHERE cl1.title = cl2.title AND cl1.id < cl2.id

Answer 3

You can simply do this:

DELETE c1 FROM clients c1 INNER JOIN clients c2 WHERE c1.id < c2.id AND c1.title = c2.title;

This query will delete the other 4 rows and keep the row with highest id.

Answer 4

Assuming you clients table have as pk the column id delete all the rows wotn same title but with id <> from max(id)

delete c
from clients c
LEFT JOIN  (
    select  max(id) max_id, title 
    from  clients 
    group by title  
) t on t.title  = c.title 
    and t.max_id  = c.id 
where t.max_id is null 

Answer 5

After using the following as suggested by forpas ...

   DELETE c 
   FROM clients c INNER JOIN clients cc
   ON cc.title = c.title AND cc.id < c.id;

... you will probably want to add a unique key to prevent duplicates in future:

    ALTER TABLE clients ADD UNIQUE KEY idx1(title);

Answer 6

For completeness, I give a solution for a table without a primary/unique key:

delete c
from clients c
cross join (select @titles := '') init
where find_in_set(sha2(title, 224), @titles) or
      length(
        @titles := concat(
          @titles, ',', sha2(title, 224)
        )
      ) < 0;

Test it online with db-fiddle.com.