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
See also  5 Benefits of Using Time Tracking Software
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist