0

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?

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.