folunude folunude - 1 month ago 6
MySQL Question

Delete all duplicates in mysql table

Consider the following table. It has been imported from CSV, it does not have a primary key.

+-----------+----------+----+----+----+
| firstname | lastname | c1 | c2 | c3 |
+-----------+----------+----+----+----+
| johnny | bravo | a | b | c |
| bruce | willis | x | y | x |
| john | doe | p | q | r |
| johnny | bravo | p | q | r |
| johnny | bravo | p | q | r |
| bruce | willis | x | y | z |
+-----------+----------+----+----+----+


I want to delete all rows where (firstname, lastname) appear more than once in the table. So the output would be:

+-----------+----------+----+----+----+
| firstname | lastname | c1 | c2 | c3 |
+-----------+----------+----+----+----+
| john | doe | p | q | r |
+-----------+----------+----+----+----+

Answer

In MySQL, the best way is to use join:

delete t
from t join
(
    select t2.firstname, t2.lastname
    from t t2
    group by t2.firstname, t2.lastname
    having count(*) > 1
) t2
    on t.firstname = t2.firstname and
       t.lastname = t2.lastname;
Comments