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

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 3

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 4

• 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

Answer 5

Not your answer, but I guess all the others (that gave answers) dont care for referential integrity... :( Just wildly deleting dupes is crazy!

Answer 6

How about this article delete duplicate records in sql server