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

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;

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

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

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