Deleting duplicate rocords in MS SQL 2000
This is a handy script for deleting records that do not have a primary key.
// the first step is to create a loop that countinues to check if there are duplicates
WHILE EXISTS( SELECT name FROM people GROUP BY name HAVING COUNT(name) > 1 )
BEGIN
// in the loop set the row count two 1 so that we only delete the first one.
SET ROWCOUNT 1
DELETE people
WHERE Name IN(SELECT name
FROM People
GROUP BY name
HAVING COUNT(name) > 1);
END
/* setting rowcount to 0 will return all rows */
SET ROWCOUNT 0