Sql-server – Subtract multiple quantities in table2 from table1

querysql serverupdate

How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.

Table1

P_ID     name       quantity
1       carrot        100
2       eggplant      100
3       Avocado       100
4       Cabbage       100
5       peppers       100

Table2

   P_ID      name       quantity
    1       carrot        3
    2       eggplant      12
    5       peppers       7
    2       eggplant      8
    1       carrot        25
    5       peppers       13

The final result should be like this:

Table1

P_ID        name     quantity
1            a         73
2            b         80
3            c         100
4            d         100
5            e         80

How can I do this? Is this even possible?

Best Answer

Here is an example of how that could be done

--Demo setup
Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;

Declare @Table2 TABLE
    (P_ID int, [name] varchar(8), quantity int)
;

INSERT INTO @Table2
    (P_ID, [name], quantity)
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7)
;

--The solution
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN @Table2 t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1

| P_ID | name | quantity |
|------|------|----------|
| 1    | a    | 97       |
| 2    | b    | 88       |
| 3    | c    | 100      |
| 4    | d    | 100      |
| 5    | e    | 93       |

Update based on OP's comment about grouping table2

Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;
Declare @Table2 table
    ([P_id] int, [name] varchar(20), [quantity] int)
;

INSERT INTO @Table2
    ([P_ID], [name], [quantity])
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7),
    (2, 'eggplant', 8),
    (1, 'carrot', 25),
    (5, 'peppers', 13)
;



;WITH Table2Grouped
AS (
    SELECT P_ID
        ,SUM(cast(quantity AS INT)) AS quantity
    FROM @Table2
    GROUP BY P_ID
    )
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN Table2Grouped t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1
Related Question