Need Help Deduping - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help Deduping

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-06-15 : 13:47:29

I just started to dedupe a table with the query below:

select [SKU Number], [Country of Ultimate Destination Code], count(*)
from US_CA_ytd_6
group by [SKU Number], [Country of Ultimate Destination Code], [2011 Cost]
having count(*) >1

The outcome shows me the duplicates, but now what do I do?

ross_gt
Starting Member

23 Posts

Posted - 2011-06-15 : 13:55:53

Ignore the 2011 Cost in the GROUP BY clause. That shouldn't be there.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-15 : 14:01:53

what do you want to do? delete? update?

If you don't have the passion to help people, you have no passion

ross_gt
Starting Member

23 Posts

Posted - 2011-06-15 : 14:03:27

Delete duplicate rows.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-15 : 16:52:44

can you please provide sample data. this will help in figuring out what to do and what to key off as follows


declare @US_CA_ytd_6  table(id int identity(1,1), [SKU Number] nvarchar(255), [Country of Ultimate Destination Code] nvarchar(255), [2011 Cost] decimal(10,2))

insert into @US_CA_ytd_6
SELECT 'V4C3D5R2', 'USA', 1556.99
UNION ALL
SELECT 'V4C3D5R3', 'Canada', 1556.99
UNION ALL
SELECT 'V4C3D5R4', 'Canada', 1556.99
UNION ALL
SELECT 'V4C3D5R3', 'Canada', 1556.99

;with dups(id, rn)
AS
(
SELECT id , 
       ROW_NUMBER() OVER(PARTITION BY [SKU Number] ORDER BY [SKU Number] DESC) as rn

  FROM @US_CA_ytd_6
)  
DELETE FROM @US_CA_ytd_6
WHERE id in 
(
SELECT id from dups where rn > 1
)

SELECT * FROM @US_CA_ytd_6

If you don't have the passion to help people, you have no passion

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-06-16 : 04:11:46

Search this site for "remove duplicates". You'll find AT LEAST a million results... (I'm probably exaggerating, but just a tad)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-17 : 18:20:49

Instead of count return max(id) from the table if there is one. Then join it with the data from the same table and delete rows where id < max_id.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources