CODEWITHSUNDEEP
mysqlsqlsql-update
Cheryl Blossom
Cheryl Blossom

Reputation: 185

How to update mutliple column in one mysql query on multiple table?

I have to update rows from the table inventory_tbl which contains total_stocks of all sizes and table product_tbl which contains total_stocks for each row. As much as possible, I want it to be done with one UPDATE query statement. This is what I came up:

UPDATE (SELECT I.product_no, O.product_sku, I.available_stock AS IA, I.reserved_stock AS IR, P.available_stock AS PA, P.reserved_stock AS PR
       FROM order_tbl O JOIN product_tbl P ON O.product_sku = P.product_sku
       JOIN inventory_tbl I ON P.product_no = I.product_no
       WHERE transaction_no = 2) AS stocks
SET stocks.IA = (stocks.IA - 1),
       stocks.IR = (stocks.IR + 1),
       stocks.PA = (stocks.PA - 1),
       stocks.PR = (stocks.PR + 1);

Update: I forgot to say that it is based if the record exists in the order_tbl. The inventory_tbl rows and product_tbl rows are updated if there is a row in order_tbl. Gomene.

Upvotes: 0

Views: 54

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below using update join

UPDATE inventory_tbl I
INNER JOIN product_tbl P ON P.product_no = I.product_no
INNER JOIN order_tbl O ON O.product_sku = P.product_sku

Set 
   I.available_stock = I.available_stock - 1,
   I.reserved_stock = I.reserved_stock - 1,
   P.available_stock = P.available_stock - 1,
   P.reserved_stock = P.reserved_stock - 1
WHERE transaction_no = 2

Upvotes: 2

Related Questions