TSQL: Remove duplicate records. Clean and Simple

It’s a common scenario, your table has several records with identical values and you need to leave only one, deleting the rest. Here is a generic TSQL query (SQL Server 2005 and above) that does just that in a few lines:

WITH DUP_TABLE AS
   (SELECT ROW_NUMBER()
    OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD1, FIELD2) As ROW_NO
    FROM ORIGINAL_TABLE)
DELETE FROM DUP_TABLE WHERE ROW_NO > 1

Here ORIGINAL_TABLE is your table with duplicates. FIELD1 and FIELD2 are columns with duplicates value (feel free to add or remove columns to suit your needs). Internal query assigns a row number to each duplicate record and DELETE statements that uses that CTE deletes all the rows except the one with Row Number = 1

Leave a Reply

Your email address will not be published. Required fields are marked *