Delete all duplicates in mysql table

Question

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  |
+-----------+----------+----+----+----+

Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — musefan, Oct 24 '16 at 11:48

score 4 · Accepted Answer · edited Oct 24 '16 at 11:49

4

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;

edited Oct 24 '16 at 11:49

Tim Biegeleisen

502,043
27
286
360

answered Oct 24 '16 at 11:48

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Oct 24 '16 at 11:48

You can select as following instead of delete.

SELECT * FROM TABLE A
INNER JOIN
(
     SELECT FirstName, LastName, COUNT(firstname) AS num
     FROM TABLE
     GROUP BY FirstName, LastName
) B ON A.FirstName = B.FirstName AND A.LastName = B.LastName
WHERE B.num = 1

But if you wanna delete then do as following

DELETE A 
FROM TABLE A
INNER JOIN
(
     SELECT FirstName, LastName, COUNT(firstname) AS num
     FROM TABLE
     GROUP BY FirstName, LastName
) B ON A.FirstName = B.FirstName AND A.LastName = B.LastName
WHERE B.num > 1

score 0 · Answer 3 · answered Oct 24 '16 at 11:49

0

delete from table_name n 
where (select count(*) from table_name z 
where n.firstname  = z.firstname  and n.lastname  = z.lastname) > 1

answered Oct 24 '16 at 11:49

Piotr Rogowski

3,642
19
24

Delete all duplicates in mysql table

3 Answers3