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?

musefan
– musefan

2016-10-24 11:48:25 +00:00
Commented Oct 24, 2016 at 11:48 — musefan
– musefan, Commented Oct 24, 2016 at 11:48
What's your PRIMARY KEY?

Strawberry
– Strawberry

2016-10-24 11:55:38 +00:00
Commented Oct 24, 2016 at 11:55 — Strawberry
– Strawberry, Commented Oct 24, 2016 at 11:55

Tim Biegeleisen · Accepted Answer · 2016-10-24 11:49:04Z

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, 2016 at 11:49

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Oct 24, 2016 at 11:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Esty · Accepted Answer · 2016-10-24 11:48:31Z

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

Piotr Rogowski · Accepted Answer · 2016-10-24 11:49:27Z

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, 2016 at 11:49

Piotr Rogowski

3,90021 silver badges24 bronze badges

Collectives™ on Stack Overflow

Delete all duplicates in mysql table

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related