Sum up duplicate, save in temp, delete, re-insert

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

General SQL Server Forums

New to SQL Server Programming

Sum up duplicate, save in temp, delete, re-insert

Author

Topic

onglim
Starting Member

2 Posts

Posted - 2011-05-03 : 12:42:00

I am still new in sql, and I am stuck after finding out
the duplicate record. I need to sum up the duplicate record
(order_qty), save record in temp table and delete the duplicate
record from the table and insert back again the record to
the table from temp table. Appreciate the help given.

LINE_NO ITEM_CODE CUSTOMER_PO ORDER_QTY
---------- ------------ -------------- ------------
43 AB067 11001952 1
44 AB067 11001952 2
45 AB070 11001952 1
46 AG137 11001952 1
47 AB088 11001959 2
48 AE015 11001959 12
49 AG123 11001959 1
50 AG137 11001959 4
51 AG137 11001959 2

Above is the partial data from DNRECEIVETEMP table

I already found the duplicate record
Sql statement I use to find out duplicate record:

SELECT * FROM Dnreceivetemp c,
(SELECT item_code, customer_po from DNRECEIVETEMP
HAving count (item_code) > 1 and count (customer_po) > 1
group by item_code, customer_po) d
WHERE c.item_code = d.Item_code
and c.CUSTOMER_PO = d.CUSTOMER_PO

Duplicate record sorted out:
LINE_NO ITEM_CODE CUSTOMER_PO ORDER_QTY
---------- ------------ -------------- ------------
43 AB067 11001952 1
44 AB067 11001952 2
50 AG137 11001959 4
51 AG137 11001959 2

got 2 duplicate record (43-44) and (50-51)
1 item can have only 1 Customer_PO (order no)
1 Customer_po (order) can have many item

I want to change the Order_QTY on line 43 and 50 and delete line 44 and 51.

LINE_NO ITEM_CODE CUSTOMER_PO ORDER_QTY
---------- ------------- -------------- ----------
43 AG137 11001959 3
50 AB067 11001952 6

Final result should be - Line_No 43 and 50 order_qty is update and Line_No 44 and 51 is delete

LINE_NO ITEM_CODE CUSTOMER_PO ORDER_QTY
---------- ------------ -------------- ------------
43 AB067 11001952 3
45 AB070 11001952 1
46 AG137 11001952 1
47 AB088 11001959 2
48 AE015 11001959 12
49 AG123 11001959 1
50 AG137 11001959 6

Assume that Line_no is not needed
I need help for sql statement on:
1. How to sum the order_qty
2. How to put the result into temp table
3. How to delete the duplicate record
4. How to insert the data back to DNRECEIVETEMP from temp table

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 13:11:44

select ITEM_CODE, CUSTOMER_PO, LINE_NO = min(LINE_NO), ORDER_QTY = sum(ORDER_QTY)
into #a
from Dnreceivetemp
group by item_code, customer_po
having count(*) > 1

update Dnreceivetemp
set ORDER_QTY = a.ORDER_QTY
from Dnreceivetemp t
join #a a
on t.item_code = a.item_code
and t.customer_po = a.customer_po
and t.LINE_NO = a.LINE_NO

delete Dnreceivetemp
from Dnreceivetemp t
join #a a
on t.item_code = a.item_code
and t.customer_po = a.customer_po
and t.LINE_NO <> a.LINE_NO

You could do this in a single statement with a cte and merge but it's probably easier like this.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

onglim
Starting Member

2 Posts

Posted - 2011-05-04 : 02:59:35

Thanks for giving the solution. Very helpful. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources