0

I need to delete all records that have duplicate values, So I would only end up with John Doe with the rows below. 

LastName, FirstName and DateofBirth

Doe        Jane          4/15/1936
Doe        Jane          4/15/1936
Doe        Jane          4/15/1936
Doe        John          6/12/1978

I used the code below but it left 2 rows. I want to delete all Jane Doe in this case since they are the same.

WITH cte AS ( 
SELECT LastName, FirstName, DateOfBirth , ROW_NUMBER() OVER(PARTITION BY LastName, FirstName, DateOfBirth
ORDER BY LastName) AS rn FROM PatientDemographics2 ) DELETE FROM cte WHERE rn > 1
John Molina
  • 179
  • 3
  • 14
  • Duplicate of [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) but replace `ROW_NUMBER()` with `COUNT`. – Thom A Sep 20 '19 at 19:52

1 Answers1

-1

The query you are using is used to remove the extra duplicates, not for deleting all the records if duplicates are presenting.

For removing all, one of the approach:

WITH cte AS ( 
SELECT LastName, FirstName, DateOfBirth , ROW_NUMBER() OVER(PARTITION BY LastName, FirstName, DateOfBirth
ORDER BY LastName) AS rn FROM PatientDemographics2 ) 
SELECT DISTINCT LastName, FirstName, DateOfBirth INTO #TEMP WHERE rn>1

DELETE A
FROM Original_table as A
INNER JOIN #TEMP  as B
ON A.LastName = B.LastName
AND A.FirstName = B.FirstName
AND A.DateOfBirht = B.DateOfBirth
LONG
  • 4,490
  • 2
  • 17
  • 35