Delete Duplicate Records - Rows - Readers Contribution

This works in 2000. WARNING: According to MS, SET ROWCOUNT will not work with INSERT, DELETE, and UPDATE in later versions.

– Create table with a number of values between zero and nine
select a+b+c as val
into dbo.rmtemp
from (select 0 a union all select 1 union all select 2 union all select 3) a
, (select 0 b union all select 1 union all select 2 union all select 3) b
, (select 0 c union all select 1 union all select 2 union all select 3) c

– Show what you’ve got
select val,count(*) row_count from dbo.rmtemp group by val

– Limit processing to a single row
set rowcount 1
– While you’ve got duplicates, delete a row
while (select top 1 val from dbo.rmtemp group by val having count(*) > 1) is not null
delete from dbo.rmtemp where val in (select top 1 val from dbo.rmtemp group by val having count(*) > 1);
– Remove single row processing limit
set rowcount 0

– Confirm that only uniques remain
select val,count(*) row_count from dbo.rmtemp group by val

– Clean up
drop table dbo.rmtemp


Reference : Pinal Dave (http://www.SQLAuthority.com)