Tuesday, June 30, 2009

How to find duplicated fields and delete them from database? (t-SQL)

If you do not want to strugle with cursors to delete duplicate records I have a simple solution for you. For this you must have a primary key or a unique key that has auto increment.

Here is the magic :

FROM CustomerTable T1, CustomerTable T2
WHERE T1.DuplicateField = T2.DuplicatedField
AND T1.UniqueField> T2.UniqueField

This will basically delete all records from the table CustomerTable which have the same value for the field DuplicateField, leaving that record which has the lowest value in UniqueField.

No comments: