devxlogo

Delete Duplicate Rows from a Table Using the Cursor

Delete Duplicate Rows from a Table Using the Cursor

This is an advanced solution to delete duplicate Rows from a table using the cursor:

 create table DupTable (    Field1 varchar(20))goinsert DupTable (Field1)    select 'A11111111' UNION ALL    select 'B22222222' UNION ALL    select 'C33333333' UNION ALL    select 'C33333333' UNION ALL    select 'C33333333' UNION ALL    select 'A11111111' UNION ALL    select 'B22222222' UNION ALL    select 'A11111111'declare @sql varchar(255),        @name varchar(32),        @count intdeclare MyCursor insensitive cursor forselect Field1, (count(1)-1) ExcssCount from DupTable group by Field1 having count(1) > 1open MyCursorfetch next from MyCursor into @name,@countwhile @@fetch_status = 0begin    select @sql =    'set rowcount ' + convert(varchar(9), @count) + '    delete DupTable where field1 = "' + @name + '"'    exec (@sql)    print @sql    fetch next from MyCursor into @name, @countend-- while fetchclose MyCursordeallocate MyCursorset rowcount 0select * from DupTabledrop table DupTable

devx-admin

Share the Post: