Friday, 1 October 2010

Delete duplicate rows sql server

Hi, here is a not to common task since you of course should not really end up with duplicate rows in a table, but, knowing that

 "The man who makes no mistakes does not usually make anything. ~Edward Phelps" 

 if you do end up with duplicates, then here is how to solve the problem. This SQL will delete the first occurrence of the duplicate entries. 

To change it so that it deletes the last entry, you would replace Min with Max.

DELETE FROM table_with_duplicates 
WHERE id IN ( 
     SELECT from table_with_duplicates AS bad_rows 
     INNER JOIN ( 
           SELECT column_1, column_2, Min(id) AS min_id FROM table_with_duplicates 
           GROUP BY column_1, column_2 HAVING COUNT (*) > 1
      ) AS good_rows 
     ON good_rows.column_1 = bad_rows.column_1 
     AND good_rows.column_2 = bad_rows.column_2 AND good_rows.min_id <>