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.
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 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 
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; 