CODEWITHSUNDEEP
sql-server-2014
John Molina
John Molina

Reputation: 321

Delete duplicate rows for greater then 1

I have a script that looks for duplicates. I want to know how now to delete those duplicates.

Select LastName, FirstName, DateOfBirth, Count (*) As Duplicates
From PatientDemographics2
Group by FirstName, LastName, DateOfBirth
Having count (*) >1
Order by LastName, FirstName Asc

Upvotes: 1

Views: 28

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28206

Or simply:

DELETE FROM PatientDemographics2 WHERE id NOT IN (
 SELECT MIN(id) i1 FROM PatientDemographics2
 GROUP BY FirstName, LastName, DateOfBirth
)

demo: https://rextester.com/KOZI16883

Upvotes: 0

Eric
Eric

Reputation: 3257

You can ROW_NUMBER() to detect duplicate

WITH cte AS (
    SELECT LastName, FirstName, DateOfBirth
        , ROW_NUMBER() OVER(PARTITION BY LastName, FirstName, DateOfBirth ORDER BY LastName) AS rn
    FROM PatientDemographic2
)
DELETE FROM cte 
WHERE rn > 1

Upvotes: 1

Related Questions