CODEWITHSUNDEEP
mysqlsqlsql-update
calvin724
calvin724

Reputation: 41

SQL update table using result of another query

I am trying to update the table(orders)after calculate the total amount of that order, I successfully calculate the total amount, but I cannot find way to update the table using the result.

code I successfully calculate the total amount:

SELECT orders.orderid, 
       SUM(ordersdish.quantity*dish.price) AS total
FROM   dish 
JOIN   ordersdish 
       ON ordersdish.dishid = dish.dishid 
JOIN   orders 
       ON orders.orderid = ordersdish.orderid 
GROUP BY orders.orderid;

Result: total amount

Code I tried to update table(orders):

UPDATE orders
SET    total = t1.total
FROM   (
        SELECT orders.orderid, 
               SUM(ordersdish.quantity*dish.price) AS total
        FROM   dish 
        JOIN   ordersdish 
               ON ordersdish.dishid = dish.dishid 
        JOIN   orders 
               ON orders.orderid = ordersdish.orderid 
       GROUP BY orders.orderid  
      )t1
 WHERE  orders.orderid = t1.orderid;

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from
(select orders.orderid, SUM(ordersdish.quantity*dish.price)as total
from ' at line 3

And here is the table "orders":

table "orders"

Upvotes: 2

Views: 105

Answers (2)

Ajeet Verma
Ajeet Verma

Reputation: 1123

Please try this answer.

UPDATE o
SET o.total = t1.total
(SELECT o.orderid, SUM(od.quantity * d.price) AS total 
FROM dish AS d 
JOIN ordersdish AS od ON od.dishid = d.dishid 
JOIN orders AS o ON o.orderid = od.orderid 
GROUP BY orders.orderid) t1
WHERE o.orderid = t1.orderid;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270201

The correct syntax in MySQL use JOIN:

update orders o join
       (select od.orderid, SUM(od.quantity * d.price)as total
        from dish d join
             ordersdish od
             on od.dishid = d.dishid  
        group by od.orderid
       ) t1
       on o.orderid = t1.orderid
    set o.total = t1.total;

Note that orders is not needed in the subquery, because the orderid is in orderdish.

Upvotes: 3

Related Questions