CODEWITHSUNDEEP
sqlduplicates
Bryan
Bryan

Reputation: 17581

Delete Duplicate SQL Records

What is the simplest way to delete records with duplicate name in a table? The answers I came across are very confusing.

Related:

Removing duplicate records from table

Upvotes: 3

Views: 2781

Answers (2)

n8wrl
n8wrl

Reputation: 19765

SQL Server 2005:

with FirstKey
AS
(
    SELECT MIN(ID), Name, COUNT(*) AS Cnt
      FROM YourTable
     GROUP BY Name
     HAVING COUNT(*) > 1
)
DELETE YourTable
  FROM YourTable YT
  JOIN FirstKey FK ON FK.Name = YT.Name AND FK.ID != YT.ID

Upvotes: 0

Bryan
Bryan

Reputation: 17581

I got it! Simple and it worked great.

delete 
   t1 
from 
   tTable t1, tTable t2 
where 
   t1.locationName = t2.locationName and  
   t1.id > t2.id

http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm

Upvotes: 5

Related Questions