Update with multiple joins in MySQL

Question

I'm having a problem updating tables in a MySQL database (InnoDB).

I have three tables

1-orders Table

orderId	userId	status
7	4	bending
8	4	bending

2- orderedProducts

orderId	productId	quantity
7	22	1
7	24	1
8	22	2
8	20	1

3- products

productId	quantity	purchased
20	1	3
22	0	1
24	1	1

Ok now when I execute the following query:-

UPDATE 
    orders JOIN 
    orderedProduct ON orders.orderId=orderedProducts.orderedId JOIN 
    products ON products.productId=orderedProducts.productId
SET
    products.quantity=products.quantity+orderedProducts.quantity,
    products.purchased=products.purchased-orderedProducts.quantity,
    orders.status="canceled"
WHERE
    orders.userId=4;

Or this

UPDATE 
    orders, orderedProducts, products 
SET
    products.quantity=products.quantity+orderedProducts.quantity
    products.purchased=products.purchased-orderedProducts.quantity,
    orders.status="canceled"
WHERE
    orders.userId=4 AND products.productId=orderedProducts.productId;

I expected the result to be like this:-

products

productId	quantity
20	2
22	3
24	2

But the result was as follows:-

productId	quantity
20	2
22	1
24	2

Does anyone have any idea about what happened?

EDIT:- The problem is solved. Thanks to every body i found the soluatiion from everyone's answer...

UPDATE 
    orders o JOIN
    (
        SELECT 
            orderId , productId , SUM(quantity) as requiredQuantity 
            FROM 
                orderedProducts 
            GROUP BY 
                productId
    ) as op ON op.orderId=o.orderId JOIN
    products as p ON op.productId=p.productId
SET
    p.quantity=p.quantity+op.requiredQuantity,
    p.purchased=p.purchased-op.requiredQuantity,
    o.status="canceled"
WHERE
    o.userId=4;

"Each matching row is updated once, even if it matches the conditions multiple times." dev.mysql.com/doc/refman/5.7/en/update.html — Paul Spiegel
– Paul Spiegel, Commented Mar 23, 2023 at 14:09

Tushar · Accepted Answer · 2023-03-23 13:46:59Z

0

Does anyone have any idea about what happened?

There are multiple rows in orderedProducts table for the same productId and so the result.

The following query shall give you the correct result as in this DBFIDDLE

UPDATE products
SET quantity = quantity + (
  SELECT SUM(quantity)
  FROM orderedProducts
  WHERE productId = products.productId
  AND orderId IN (
    SELECT orderId
    FROM orders
    WHERE userId = 4
  )
);

Output :

select * from products;

productId	quantity
20	2
22	3
24	2

Well, if you want to do the same using join; You can do it as in this DBFIDDLE:

    UPDATE products
JOIN (
    SELECT orderedProducts.productId, SUM(orderedProducts.quantity) AS total_quantity
    FROM orderedProducts
    JOIN orders ON orderedProducts.orderId = orders.orderId
    WHERE orders.userId = 4
    GROUP BY orderedProducts.productId
) AS ordered
ON products.productId = ordered.productId
SET products.quantity = products.quantity + ordered.total_quantity;

This also shall give the expected output

Update :

Based on your comment :

In the Products table, there is another column that I want to modify (purchased). Also, in the Orders table, there is a column that I want to modify (status). I think in this case it is better to use JOIN, but it ignores the duplicate productId. Is there a specific formula that does not ignore duplicate values?

You can update it as below :

UPDATE products p
JOIN orderedProducts op ON p.productId = op.productId
JOIN orders o ON op.orderId = o.orderId
SET p.quantity = p.quantity + op.quantity,
p.purchased = p.purchased + op.quantity,
o.status = 'Processed'
WHERE o.userId = 4;

edited Mar 23, 2023 at 13:46

answered Mar 23, 2023 at 13:21

Tushar

3,6759 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marwan Over a year ago

this works well, but in the question, I simplified the tables. In the Products table, there is another column that I want to modify (purchased). Also, in the Orders table, there is a column that I want to modify (status). I think in this case it is better to use JOIN, but it ignores the duplicate productId. Is there a specific formula that does not ignore duplicate values?

Tushar Over a year ago

I have re-written the query using joins. Can you please check it now ?

Marwan Over a year ago

Thank you, but there is still one problem. I can not modify the (status) column in orders table.

Tushar Over a year ago

So,

o.status = 'Processed'``Can we remove from above query  along with presiding comma:  Or is there any restriction in updating

orders.status` column ?

Marwan Over a year ago

sorry, i did not see last edit you made. Based on the latest modification, we will return to the same first problem. Duplicate values will be ignored when using (join).

SelVazi · Accepted Answer · 2023-03-23 13:49:29Z

0

This query will give you the updated quantity of every product :

select p.productId, sum(distinct p.quantity) + sum(op.quantity)
from products p
inner join orderedProducts op on op.productId = p.productId
inner join orders o on o.orderId = op.orderId
WHERE o.userId=4
group by p.productId

Using the previous query, the updated query could be as follows :

Update products p
inner join (
  select p.productId, sum(distinct p.quantity) + sum(op.quantity) as newQuantity
  from products p
  inner join orderedProducts op on op.productId = p.productId
  inner join orders o on o.orderId = op.orderId
  WHERE o.userId=4
  group by p.productId
) s on s.productId = p.productId
set quantity = s.newQuantity

Demo here

edited Mar 23, 2023 at 13:49

answered Mar 23, 2023 at 13:33

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

4 Comments

Paul Spiegel Over a year ago

This is not correct: sum(p.quantity + op.quantity). You are adding p.quantity per order and only get the right result, because of "lucky" sample data.

SelVazi Over a year ago

Correct, thanks for your remarks :) I have updated my answer

Paul Spiegel Over a year ago

sum(distinct .. ) will probably work, but it could also just be p.quantity + sum(op.quantity). You might need to include p.quantity in the ORDER BY clause - But it's not needed in MySQL if p.productId is the PK.

Marwan Over a year ago

Thank you for your answer. I edited the question to be more clear about what I want..

Collectives™ on Stack Overflow

Update with multiple joins in MySQL

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related