Question 20352 in Mysql Question posted in
The official MySQL documentation can be found here.

Update with multiple joins in MySQL

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;
Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search