CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Sam
Sam

Reputation: 1403

Deleting Duplicate Records from a Table

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.

Upvotes: 5

Views: 505

Answers (3)

bobs
bobs

Reputation: 22184

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.

Upvotes: 5

Raymund
Raymund

Reputation: 7892

This is an easier way

Select * Into #TempTable FROM  YourTable
Truncate Table YourTable
Insert into YourTable Select Distinct * from #TempTable
Drop Table #TempTable

Upvotes: 0

Sam
Sam

Reputation: 7678

http://support.microsoft.com/kb/139444

This section is the key. The primary point you should take away. ;)

This article discusses how to locate and remove duplicate primary keys from a table. However, you should closely examine the process which allowed the duplicates to happen in order to prevent a recurrence.

Identify your records by grouping data by your logical keys, since you obviously haven't defined them, and applying a HAVING COUNT(*) > 1 statement at the end. The article goes into this in depth.

Upvotes: 0

Related Questions