Wednesday, September 28, 2011

Oracle : Delete duplicate records from a table

This is a very simple script to perform a task which almost every other database developer faces in his day to day job. To finding and deleting duplicate records. I would try to answer it in 3 simpler steps

Step1: Lets say you want to find the duplicate records for a pair of attributes c1 and c2
Find all duplicate records:


SELECT c1, c2,count(1) FROM MyTable GROUP BY c1,c2

Step 2: Finding the row that you want to keep. Lets say  you want to keep only the maximum value of some indentifier column




SELECT c1, c2,max(id) FROM MyTable GROUP BY c1,c2



Step 3: keep that and delete others
Delete from mytable
where id not in (
SELECT c1, c2,max(id)


FROM MyTable
GROUP BY c1,c2



)