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 (
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
ROW_NUMBER() OVER ( PARTITION BY UPC ORDER BY UPC) AS ROWNUM
) DELETE FROM cte WHERE ROWNUM > 1;
________________________________________ -- Yes, I am indeed a fictional character.