Remove duplicate records from the database table
Posted by Viral Sarvaiya on October 18, 2010
In my recent post https://viralsarvaiya.wordpress.com/2010/09/27/removing-duplicate-records-from-datasetdatatable/, I explain how to remove duplicate records form the dataset or datatable.
But if you want to delete duplicate records directly from the database then…..
First find the duplicate records,
SELECT MAX(ID) as ID, Name, Company FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1
This records give me the total duplicate records
Now to delete the duplicate records,
DELETE FROM Employee WHERE ID IN (SELECT MAX(ID) FROM Employee GROUP BY Name, Company HAVING COUNT(*) > 1)