DELETE DUP REC"S

DELETE DUP REC"S

create table A (I int, J int, K int)

insert A select 1,1,1
insert A select 1,1,1
insert A select 1,1,1
insert A select 1,1,1
insert A select 2,1,1
insert A select 2,1,1
insert A select 2,2,1
insert A select 2,2,2
insert A select 2,2,3
insert A select 2,2,4
insert A select 3,3,3
insert A select 3,3,3
insert A select 3,3,3

select * from A

1. simple table recreate


select * into B from A where 1 = 0
insert B select distinct * from A
begin tran
delete A
insert A select * from B
commit tran
drop table B

2. delete and replace duplicates

select * into B from A where 1 = 0
insert B select i,j,k from A group by i,j,k having count(*) > 1
begin tran
delete A from B where A.i = B.i and A.j = B.j and A.k = B.k
insert A select * from B
commit tran
drop table B

3. delete duplicates one by one leaving single row
set rowcount 1
select 1
while @@rowcount > 0
delete A
where 1 < (select count(*) from A a2 whereA.i = a2.i and A.j = a2.j and A.k = a2.k)
set rowcount 0

4. delete all duplicates for one row value at a time
select *, cnt = 0 into B from A where 1 = 0
declare @rowcount int
select 1
while @@rowcount <> 0
begin
insert B (i,j,k,cnt) select top 1 i,j,k, count(*) - 1 from A group by i,j,k having count(*) > 1
select @rowcount = cnt from B
set rowcount @rowcount
delete