Delete Duplicate Row in sql server
create table test(name varchar(20),rollNo int)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
SET ROWCOUNT 0 --execute rowcount 0 after executing the cursor
select * from test order by name
--Create cursor for duplicate row delete
Declare @Count int
DECLARE @rollNo int
DECLARE @name nvarchar(20)
DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, rollNo, Count(*) - 1
FROM test
GROUP BY name, rollNo
HAVING Count(*) > 1
OPEN dublicate_cursor
FETCH NEXT FROM dublicate_cursor INTO @name,@rollNo,@Count
WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @count
--DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo
DELETE FROM test WHERE name = @name AND rollNo = @rollNo
FETCH NEXT FROM dublicate_cursor INTO @name, @rollNo, @Count
END
CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor
Note:- SET ROWCOUNT @count is used when you are using sql server 2000
In sql server 2005 you can use DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo
Thanks and enjoy
