May 4, 2010 at 7:36 pm
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
May 4, 2010 at 7:55 pm
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
May 4, 2010 at 8:38 pm
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!!
May 4, 2010 at 11:33 pm
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]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 5, 2010 at 4:28 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply