CODEWITHSUNDEEP
mysql
Kate Jhon
Kate Jhon

Reputation: 11

Update query subtract same sale model rows from stock table?

I have two MySQL tables:

> Sale 
--------------------------------------------------------
 ID      customer       model                    qty
--------------------------------------------------------
 1         Ali          H46                      1
 1         aslam        H46                      1
 1         kasif        H46                      1
 1         umer         H46                      1
 1         naveed       H46                      1
---------------------------------------------------------

> Stock

---------------------------------------------------------
ID        model               qty
---------------------------------------------------------
1         H46                 40
1         H47                 30
1         H48                 20
1         H49                 60
1         H50                 20
---------------------------------------------------------

MySQL query I applied

UPDATE sale AS T1
INNER JOIN stock T2
    ON T1.model = T2.model
SET T2.qty = (T2.qty - T1.qty)

In result in stock table only one qty update doesn't total sale qty minus from stock table.

I want all sale qty minus from stock. But my query result return 1 record update in stock.

Upvotes: 0

Views: 337

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

I think you need to join to an aggregate of the Sale table:

UPDATE stock st
INNER JOIN
(
    SELECT model, SUM(qty) AS qty
    FROM sale
    GROUP BY model
) s
    ON st.model = s.model
SET
    st.qty = st.qty - s.qty;

An inner join update (rather than a left join update) should be fine here, since models which had zero sales would not require updating the stock anyway.

Upvotes: 0

Related Questions