How to update table multiple times based on another table

Question

I'm trying to update a table multiple times based on data from another table.
Below is the table design I have at the moment:

Table A                          Table B
--------------------             --------------------
Goods    QTY    Type             Goods    QTY    Type
Wood      0       R1             Wood       1      R1
Wood     10       R2             Wood       4      R1
Glass    10       R1             Wood       5      R1
Glass     0       R2             Glass      5      R2
                                 Glass      4      R2

I would like to do the addition on QTY in Table A based on QTY from Table B, if possible in one update statement.

Expected output is something like this:

Table A
--------------------
Goods    QTY    Type
Wood     10       R1 ---> Previous value: 0
Wood     10       R2
Glass    10       R1
Glass     9       R2 ---> Previous value: 0

I already tried the below update statement to no avail:

UPDATE Table A
SET A.QTY = A.QTY + B.QTY
FROM Table A as A JOIN Table B as B ON A.Goods = B.Goods AND A.Type = B.Type

However, the above query statement is only updating the first distinct value on Table B. It only update the QTY of Wood to 1, and QTY of Glass to 5.

You could use a common table expression. This solution is described here: https://stackoverflow.com/a/1604212/245915. — nofinator, Mar 29 '19 at 17:59

score 1 · Accepted Answer · answered Mar 29 '19 at 18:05

1

You could use an inline subquery, like:

UPDATE TableA A
SET A.QTY = A.QTY + (
   SELECT SUM(B.QTY) FROM TableB B WHERE A.Goods = B.Goods AND A.Type = B.Type
)

answered Mar 29 '19 at 18:05

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Mar 29 '19 at 18:07

First, aggregate Table B by summing the Qty field for each unique combination of Goods & Type using a combination of the sum function and group by clause.

select t.goods, t.type, sum(t.qty) as tqty
from [table b] t
group by t.goods, t.type

Then join this subquery to Table A:

update [table a] a join
(
    select t.goods, t.type, sum(t.qty) as tqty
    from [table b] t
    group by t.goods, t.type
) b 
on a.goods = b.goods and a.type = b.type
set a.qty = a.qty + b.tqty

How to update table multiple times based on another table

2 Answers2