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

Share

Share

Rate