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.