Deleteing the duplicate Records

[ganeshkumar08]

SET ROWCOUNT 1
delete from dbo.[User]
where ID IN (select id from dbo.[User]
group by(id) having(count(id) > 1))
WHILE @@rowcount > 0
BEGIN
delete from dbo.[User]
where ID IN (select id from dbo.[User]
group by(id) having(count(id) > 1))
END
SET ROWCOUNT 0

The above query is for deleting the duplicate records. It is working well.
I got this query from one of the site,
can any one explain this query how it executes.

Thanks
Ganesh Kumar

[Delerna]

this bit is a subquery or derived table

(select id from dbo.[User] 
group by(id) having(count(id) > 1))
 

it contains a list of all id's that have more than 1 record. In other words a list of the id's that need to be deleted
the delete is deleting records in the table that have its id listed in the derived table

if any records were deleted the system variable @@rowcount will contain the number of records that were deleted

it then enters a loop that that continually deletes duplicate rows untill no records were deleted. When that happens @@rowcount will equall 0 and the while loop is ended.