Delete duplicate rows

Question

Delete duplicate rows

mbrady5

SSCrazy

Points: 2835
More actions
May 4, 2010 at 7:36 pm

#234654

Hello, I would like to delete duplicate rows based on the location_number column in my table having a count >1.
I was able to create a query to locate these records, but now I want to delete the dupes. I was hoping for someone to help me design the delete query. Below is the query where I was able to find the dupes. Thanks
SELECT LOCATION_NUMBER, COUNT(*) TotalCount
FROM ForResiMailing
GROUP BY LOCATION_NUMBER
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 1

Since I am not completely sure of your table structure given the information provided I am assuming that their is some sort of PK on the records. I am using "ID" in the script below to represent that. Test this against a backup of the table to ensure the results are what you are expecting. Don't forget to change "ID" to your PK.

WITH DupeRecords

AS

(

SELECT ID,ROW_NUMBER OVER (PARTITION BY LOCATION_NUMBER ORDER BY ID) AS RowNum

FROM ForResiMailing

)

DELETE FROM ForResiMailing a JOIN DupeRecords b ON a.ID = b.ID

WHERE b.RowNum > 1

[/Code]

Regards,

Jason P. Burnett
Senior DBA

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Jason's code will perfectly delete the duplicate rows.

As a side note, mbrady5, please post the table structures (create table scripts), some sample data cokked-up from your original data (as insert into table scripts), clear-cut representation of desired results! This way you will get the ultimate code for your requirement!

Cheers!!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Jason P. Burnett (5/4/2010)
Since I am not completely sure of your table structure given the information provided I am assuming that their is some sort of PK on the records. I am using "ID" in the script below to represent that. Test this against a backup of the table to ensure the results are what you are expecting. Don't forget to change "ID" to your PK.
WITH DupeRecords
AS
(
SELECT ID,ROW_NUMBER OVER (PARTITION BY LOCATION_NUMBER ORDER BY ID) AS RowNum
FROM ForResiMailing
)
DELETE FROM ForResiMailing a JOIN DupeRecords b ON a.ID = b.ID
WHERE b.RowNum > 1
[/Code]

You can remove the JOIN in the above query this way..

WITH DupeRecords

AS

(

SELECT ROW_NUMBER OVER (PARTITION BY LOCATION_NUMBER ORDER BY ID) AS RowNum, *

FROM ForResiMailing

)

DELETE FROM DupeRecords WHERE RowNum > 1

[/Code]

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 4

Kingston Dhasian (5/4/2010)
You can remove the JOIN in the above query this way..
WITH DupeRecords
AS
(
SELECT ROW_NUMBER OVER (PARTITION BY LOCATION_NUMBER ORDER BY ID) AS RowNum, *
FROM ForResiMailing
)
DELETE FROM DupeRecords WHERE RowNum > 1
[/Code]

Excellent point Kingston! I completely forgot about the CTE haveing the same updateable rules as a view. Since it only includes a single table it is directly updateable.

Regards,

Jason P. Burnett
Senior DBA