Delete duplicated records in Sql Server 2000
Mar 7th, 2008 by Onyrix 204 Views | 
Email This Post
| 
Print This Post
(5 votes, average: 4.6 out of 5)
Loading ...Suppose to have mytable with a key k1,k2,k3 and not unique key specified onto it
Apply the following code N times (until the records affected are 0) to the table to delete the duplicated records:
declare @k1 char(2)
declare @k2 int
declare @k3 varchar(4)
declare @con intdeclare c1 cursor for
select k1,k2,k3,count(*) from mytable
GROUP BY k1,k2,k3
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1 INTO @k1, @k2, @k3, @con
WHILE @@FETCH_STATUS = 0
BEGIN
set rowcount 1
delete from mytable where k1=@k1 and k2=@k2 and k3=@k3
set rowcount 0
FETCH NEXT FROM c1 INTO @k1, @k2, @k3, @con
END
CLOSE c1
DEALLOCATE c1
del.icio.us
Digg
Furl
Reddit
Technorati
Stumbleupon