CODEWITHSUNDEEP
mysqlsql
folunude
folunude

Reputation: 3

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

Upvotes: 0

Views: 108

Answers (3)

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 4

Esty
Esty

Reputation: 1912

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

Upvotes: 0

Related Questions