I have a table in my database which has many duplicate rows. I'd like to keep one of the rows in that list and delete the remainder. How do I do this? When I want to eliminate all duplicates, I use the following code. I just can't think of how to modify it to work here.
DELETE FROM MyDB.dbo.Table
WHERE UPC IN (
SELECT UPC
FROM MyDB.dbo.Table
WHERE LEN(UPC)>7
GROUP BY UPC
HAVING COUNT(UPC) > 1
)
I have often used the ROW_NUMBER function to do this. In the code below, the red UPC indicates the column(s) that you use to determine uniqueness. It does not necessarily have to be just one column, you can list any or all columns there depending on your rules for determining whether something is duplicate or not.
The "ORDER BY UPC" is an ordering that I picked randomly for you. The row that comes first in the ordering scheme will be retained, all others will be deleted. You can change that as you wish - for example, if you had a datestamp column in your table, and you wanted to retain the latest row for each UPC based on that datestamp, you would change the order by clause to "ORDER BY datestamp DESC":
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY UPC ORDER BY UPC) AS ROWNUM
FROM
MyDB.dbo.Table
) DELETE FROM cte WHERE ROWNUM > 1;
________________________________________ -- Yes, I am indeed a fictional character.
Delete T
FROM MyDB.dbo.Table T
Where Exists
(
Select *
From MyDB.dbo.Table TBL
Where TBL.UPC = T.UPC
Group by TBL.UPC
Having Count(*) > 1
And Min(TBL.PrimaryKey) = T.PrimaryKey
)
How do I keep one of many duplicate rows?
Posted - 11/23/2012 : 21:15:52
