Technical Article

Remove Duplicates from Table - No Cursors

,

This script deletes all duplicates from a table.  It keeps the first instance of the record, and discards all others.  If you want to keep the last instance of the record, use MAX instead of MIN. 

The script can be modified very easily to include more/less fields for comparison.  I've successfully used this SP to find dupes in over 100 fields.  The only requirement is that the table must have a unique ID field.

DELETE FROM
t1
FROM
MyTable t1
INNER JOIN
(
SELECT
MIN(FieldID) AS FieldID,
FieldWithDupes1,
FieldWithDupes2,
FieldWithDupes3
FROM
MyTable
GROUP BY
FieldWithDupes1,
FieldWithDupes2,
FieldWithDupes3
HAVING
COUNT(*) > 1
) t2
ON(
t1.FieldWithDupes1 = t2.FieldWithDupes1
AND t1.FieldWithDupes2 = t2.FieldWithDupes2
AND t1.FieldWithDupes3 = t2.FieldWithDupes3
AND t1.FieldID <> t2.FieldID
)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating