RSS Feed

Delete Duplicate Records


Following are the 3 ways to Delete Duplicate Records

A.
How to remove duplicate rows from a table in SQL Server.

Microsoft

B.
Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

If there is no identity column then

Add Identity Col and perform the operation displayed above and drop Identity Col.

[Source]

C.
Removing Duplicates from a Table in SQL Server

Simple Talk

This above post describes many ways to delete duplicate records like:

1. Deleting duplicate records from tables with no primary key.
2. Deleting duplicate records from tables with a primary key, but no foreign key constraints.
3. Deleting duplicate records from tables that are referenced by a Foreign Key.
4. Deleting duplicate records from tables with columns that cannot have a UNIQUE constraint.
5. Using a cursor to delete duplicate records.
6. New Techniques for Removing Duplicate Rows in SQL Server 2005.