Removing duplicates from a table without using a temporary table

Question

I have a table (TableA) with contents like this:

Col1 ----- A B B B CC D 

I want to delete only duplicate values without using a temporary table in Microsoft SQL Server . Can someone help me? The final table should look like this:

 Col1 ----- A B CD 

thanks:)

Answer 1

 WITH TableWithKey AS ( SELECT ROW_NUMBER() OVER (ORDER BY Col1) As id, Col1 As val FROM TableA ) DELETE FROM TableWithKey WHERE id NOT IN ( SELECT MIN(id) FROM TableWithKey GROUP BY val ) 

Answer 2

Can you use the row_number () function ( http://msdn.microsoft.com/en-us/library/ms186734.aspx ) to split the columns you are looking for cheats and delete where the line number is not equal?

Answer 3

I completely agree that having a unique identifier will save you a lot of time.

But if you cannot use one (or if it is purely hypothetical), here is an alternative: Determine the number of rows to delete (the number of each individual value is -1), then run a loop and delete the top X for each individual value.

Please note that I am not responsible for the number of kittens that kill every time you use dynamic SQL.

 declare @name varchar(50) declare @sql varchar(max) declare @numberToDelete varchar(10) declare List cursor for select name, COUNT(name)-1 from #names group by name OPEN List FETCH NEXT FROM List INTO @name,@numberToDelete WHILE @@FETCH_STATUS = 0 BEGIN IF @numberToDelete > 0 BEGIN set @sql = 'delete top(' + @numberToDelete + ') from #names where name=''' + @name + '''' print @sql exec(@sql) END FETCH NEXT FROM List INTO @name,@numberToDelete END CLOSE List DEALLOCATE List 

Another alternative would be to create a view with a generated identifier. Thus, you can match the values ​​with a unique identifier (with the possibility of regular deletion) without constantly adding to the table.

Answer 4

Select the grouped data in the temp table, then crop the original, and then return it to the original.

The second solution, I'm not sure if this will work, but you can try to open the table directly from SQL Management Studio and use CTRL + DEL for the selected rows to delete them. It will be very slow because you need to delete each line with your hands.

Answer 5

You can delete duplicate lines with the cursor and DELETE .. WHERE CURRENT OF .

 CREATE TABLE Client ([name] varchar(100)) INSERT Client VALUES('Bob') INSERT Client VALUES('Alice') INSERT Client VALUES('Bob') GO DECLARE @history TABLE (name varchar(100) not null) DECLARE @cursor CURSOR, @name varchar(100) SET @cursor = CURSOR FOR SELECT name FROM Client OPEN @cursor FETCH NEXT FROM @cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN IF @name IN (SELECT name FROM @history) DELETE Client WHERE CURRENT OF @cursor ELSE INSERT @history VALUES (@name) FETCH NEXT FROM @cursor INTO @name END