Got this from someone on this site. I cannot take credit for it.
I cannot remember who it was...
ALTER TABLE TableWithDuplicates ADD EliminateDuplicates_RowID int NOT NULL IDENTITY(1, 1)
DELETE
FROM t1
FROM TableWithDuplicates t1
JOIN(
SELECT EliminateDuplicates_RowID=MAX(EliminateDuplicates_RowID),
Field1,
Field2,
Field3,
Field4
FROM TableWithDuplicates
GROUP BY Field1,
Field2,
Field3,
Field4
HAVING COUNT(*) >= 2
) t2
ON( t1.EliminateDuplicates_RowID <> t2.EliminateDuplicates_RowID
AND t1.Field1 = t2.Field1
AND t1.Field2 = t2.Field2
AND t1.Field3 = t2.Field3
AND t1.Field4 = t2.Field4)
ALTER TABLE TableWithDuplicates DROP COLUMN EliminateDuplicates_RowID
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!