Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
SergioM
Posting Yak Master
170 Posts |
Posted - 2012-11-23 : 21:15:52
|
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.TableWHERE UPC IN ( SELECT UPC FROM MyDB.dbo.Table WHERE LEN(UPC)>7 GROUP BY UPC HAVING COUNT(UPC) > 1 ) -SergioI use Microsoft SQL 2008 |
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-23 : 21:56:14
|
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. |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2012-11-24 : 11:19:28
|
This is exactly what I needed. Thanks!-SergioI use Microsoft SQL 2008 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-24 : 12:30:11
|
[code]Delete T FROM MyDB.dbo.Table TWhere 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 )[/code] |
|
|
|
|
|