CODEWITHSUNDEEP
sqlsql-server
Penguen
Penguen

Reputation: 17298

How do I delete duplicate data in SQL Server?

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?

Upvotes: 0

Views: 665

Answers (5)

coder net
coder net

Reputation: 3475

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

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 2

Eric Petroelje
Eric Petroelje

Reputation: 60559

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.

Upvotes: 7

Ferdinand Beyer
Ferdinand Beyer

Reputation: 67227

  • 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

Upvotes: 0

Bravax
Bravax

Reputation: 10493

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.

Upvotes: 0

Related Questions