CODEWITHSUNDEEP
mysqlduplicatesrows
Neeraj
Neeraj

Reputation: 9205

Remove Duplicate Records in Mysql Only

I have a following table 

------------
id name
------------
1  master
2  datagrid 
3  zewa
4  leumas
5  delta
6  master
7  master
8  master
9  delta

I just want to remove duplicate (repeating) rows. So from the above table, all the rows for "master" and "delta" should be removed.

Note: I don't want to use temporary table or any Alter statement. I just want to use a Delete query

Upvotes: 1

Views: 3384

Answers (2)

satdev86
satdev86

Reputation: 810

DELETE from table where name in (select name from table group by name having count(name)>1);

Upvotes: -2

Martin Smith
Martin Smith

Reputation: 453910

IIRC MySQL does not allow you to reference the mutating table in an IN clause except by adding an additional layer of indirection.

DELETE FROM YourTable
WHERE  name IN (SELECT name
                FROM   (SELECT name
                        FROM   YourTable
                        GROUP  BY name
                        HAVING COUNT(name) > 1) AS T)

Upvotes: 8

Related Questions