Remove duplicates and update quantities

[WelshyWizard]

Hi all,

I've imported a csv file into SQL Server 2000 and it is riddled with duplicates. Each record has a quantity and what I want to do is remove the records that are duplicated and finally end up with one record which has an updated quantity.

So, for example, part P12 is found on 3 records, each as a quantity of 2. I want some SQL code to remove 2 of the records and update the quantity of the remaining record to be 6.

Any ideas?

Today is the tomorrow you worried about yesterday - and all is well.....

 

[ca8msm]

You could use your initial table as a staging table and then insert the grouped by records into the real table e.g.

DECLARE @TABLE1 TABLE (id int, description varchar(10), quantity int)
DECLARE @TABLE2 TABLE (id int IDENTITY(1,1), description varchar(10), quantity int)

INSERT @TABLE1 VALUES (1, 'Item 1', 2)
INSERT @TABLE1 VALUES (2, 'Item 1', 2)
INSERT @TABLE1 VALUES (3, 'Item 1', 2)
INSERT @TABLE1 VALUES (4, 'Item 2', 2)
INSERT @TABLE1 VALUES (5, 'Item 3', 2)

INSERT @TABLE2 
SELECT description, sum(quantity) as quantity
FROM @TABLE1
GROUP BY description

SELECT id, description, quantity 
FROM @TABLE2

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.