Tutorial :How do I delete duplicate data in SQL Server?



Question:

I have sql table but, some value added more than once, I need to delete one of them. Can you give me a simple query?


Solution:1

From here. If you don't already have an "ID" field that uniquely identifies each row, you'll need to create one for this to work (you can always just drop the column after you are done):

DELETE  FROM MyTable  WHERE ID NOT IN  (  SELECT MAX(ID)  FROM MyTable  GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)  

Also, do a search on this site for "delete duplicate rows sql server" and you'll see that this question has already been answered many times here.


Solution:2

DECLARE @Duplicates TABLE (AValue VARCHAR(32))    INSERT INTO @Duplicates VALUES ('No Duplicate')  INSERT INTO @Duplicates VALUES ('Duplicate')  INSERT INTO @Duplicates VALUES ('Duplicate')      SELECT ID = IDENTITY(INT, 1, 1), *   INTO #Duplicates  FROM @Duplicates    DELETE FROM #Duplicates  FROM #Duplicates d       INNER JOIN (         SELECT ID = MIN(ID)         FROM #Duplicates         GROUP BY AValue         HAVING COUNT(*) > 1        ) id ON id.ID = d.ID    DELETE FROM @Duplicates    INSERT INTO @Duplicates  SELECT AValue FROM #Duplicates    DROP TABLE #Duplicates    SELECT * FROM @Duplicates  


Solution:3

;WITH tempTable AS         (                SELECT ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2 ) AS rownumber,*                 FROM @Table        )        DELETE FROM tempTable WHERE rownumber > 1   

This is taken from the article link mentioned in one of the answers but just to save someone sometime in the future..


Solution:4

Use:
Set RowCount 1

Then run your delete sql

This will delete 1 row, regardless if there are multiple rows matching.

Then either close the query window, or use Set RowCount 0 to reset it.


Solution:5

  • Create temporary table with the same schema
  • INSERT INTO temptable SELECT DISTINCT * FROM oldtable
  • DELETE FROM oldtable
  • INSERT INTO oldtable SELECT * FROM temptable
  • DROP temptable

And don't forget:

  • Refactor your tables to not allow this to happen again

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »