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?

Answer 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.

Answer 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

Answer 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..

Answer 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.

Answer 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