By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Deleteing the duplicate Records

ganeshkumar08
P: 31
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
Apr 9 '08 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
this bit is a subquery or derived table

Expand|Select|Wrap|Line Numbers
  1. (select id from dbo.[User] 
  2. group by(id) having(count(id) > 1))
  3.  
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.
May 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.