CODEWITHSUNDEEP
mysqlsqlsql-updatemultiple-tables
Marwan
Marwan

Reputation: 84

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;

Upvotes: 0

Views: 83

Answers (2)

SelVazi
SelVazi

Reputation: 16063

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

Upvotes: 0

Tushar
Tushar

Reputation: 3623

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;

Upvotes: 0

Related Questions