delete duplicate entries in table

Question

I want to delete multiple duplicate keys from the below table:

id | name   | uid
1  | ekta   | 5
2  | ekta   | 5
3  | sharma | 10
4  | sharma | 10

want it to be like

id | name   | uid
1  | ekta   | 5
3  | sharma | 10

I am using mysql. Is it ossible.? I can't use unique constraint query to make unique enteries because i want this duplicate entries ones entered to the table.

look at this http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — sghazanfar, Mar 20 '13 at 06:05

John Woo · Accepted Answer · 2013-03-20T06:24:42.560

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID
            FROM    TableName
            GROUP   BY uid
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID
WHERE   b.uid IS NULL

SQLFiddle Demo

However, if the records of UID can have different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID, name
            FROM    TableName
            GROUP   BY uid, name
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID AND
                a.name = b.name
WHERE   b.uid IS NULL

i have to fine same name and uid and then have to delete duplicate record so its bit complicated. :( — Ekta Sharma, Mar 20 '13 at 06:14

score 0 · Answer 2 · answered Mar 20 '13 at 06:02

  DELETE DupRows.*
  FROM MyTable AS DupRows
  INNER JOIN (
            SELECT MIN(ID) AS minId, col1, col2
            FROM MyTable
            GROUP BY col1, col2
            HAVING COUNT(*) > 1
             ) AS SaveRows 
  ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2
  AND SaveRows.minId <> DupRows.ID;

delete duplicate entries in table

2 Answers2

Linked