I have a table with a id, item_id and qty(quantity) column. I want to “merge/combine” the rows that match on the item_id. When I do this I want to SUM
the qty per item_id update one row and remove the other rows. I found this solution but I was hoping for a single transaction solution(should I just put it in a procedure or function?). I was looking at the MERGE
function but I was not able to figure out how to run both a update and a delete when we had a match, it also appeared to me that it was for merging two tables. I am sure there has to be a way to do it, it just seems to be outside my grasp.
Original dataset
The transaction method taken from the previous link.
UPDATE test t
SET qty = s.qty
FROM (SELECT MIN(id) id, SUM(qty) qty
FROM test
GROUP BY item_id) s
WHERE s.id = t.id
DELETE FROM test t1
WHERE t1.id > (SELECT MIN(t2.id)
FROM test t2
WHERE t2.item_id = t1.item_id)
Results after the first transaction.
Results after the 2nd and final transaction. This is a result I am happy with.