Deleting Duplicate Records from a Table

Question

I Have a table called Table1 which has 48 records. Out of which only 24 should be there in that table. For some reason I got duplicate records inserted into it. How do I delete the duplicate records from that table.

Answer 1

Here's something you might try if SQL Server version is 2005 or later.

WITH cte AS
    (
    SELECT {list-of-columns-in-table},
      row_number() over (PARTITION BY {list-of-key-columns} ORDER BY {rule-to-determine-row-to-keep}) as sequence
    FROM myTable
    )

DELETE FROM cte
WHERE sequence > 1

This uses a common table expression (CTE) and adds a sequence column. {list-of-columns-in-table} is just as it states. Not all columns are needed, but I won't explain here.

The {list-of-key-columns] is the columns that you use to define what is a duplicate.

{rule-to-determine-row-to-keep} is a sequence so that the first row is the row to keep. For example, if you want to keep the oldest row, you would use a date column for sequence.

Here's an example of the query with real columns.

WITH cte AS
    (
    SELECT ID, CourseName, DateAdded,
        row_number() over (PARTITION BY CourseName ORDER BY DateAdded) as sequence
    FROM Courses
    )

DELETE FROM cte
WHERE sequence > 1

This example removes duplicate rows based on the CoursName value and keeps the oldest basesd on the DateAdded value.