Update data in Multiple table

Question

Three table as follow

mysql> select * from food;
+--------+------+-------+
| foodid | name | price |
+--------+------+-------+
|      1 | 雞   |   100 |
|      2 | 鴨   |   200 |
|      3 | 魚   |   300 |
|      4 | 肉   |   400 |
+--------+------+-------+
4 rows in set

mysql> select * from drink;
+---------+------+-------+
| drinkid | name | price |
+---------+------+-------+
|       1 | 紅茶 |    50 |
|       2 | 綠茶 |   100 |
|       3 | 奶茶 |   150 |
+---------+------+-------+
3 rows in set

mysql> select * from order_table;
+----+-----------+--------+---------+------------+-------------+-------------+
| id | user_name | foodid | drinkid | food_count | drink_count | total_price |
+----+-----------+--------+---------+------------+-------------+-------------+
|  2 | 小明      |      3 |       2 |          2 |           2 |           0 |
|  3 | 小華      |      1 |       1 |          1 |           8 |           0 |
|  4 | 小英      |      1 |       3 |          3 |           3 |           0 |
|  6 | 小a       |      2 |       1 |          4 |           6 |           0 |
|  7 | 小b       |      2 |       2 |          5 |           4 |           0 |
|  8 | 小c       |      2 |       3 |          6 |          10 |           0 |
|  9 | 大A       |      3 |       1 |          9 |           8 |           0 |
| 10 | 大B       |      3 |       2 |          5 |           4 |           0 |
| 11 | 大C       |      3 |       3 |         10 |           3 |           0 |
+----+-----------+--------+---------+------------+-------------+-------------+

foodid in order_table is link to foodid in food table,
drinkid in order_table is link to drinkid in drink table,

Now, I want to calculate total price,

Total_price = 
    order_table.foodid(food.price in food table) * order_table.food_count +
    order_table.drinkid(drink.price in drink table) * order_table.drink_count;

So, let me knowlege the command to update total price

thx a lot.

Do you want to know how to retrieve the data necessary to calculate the total price, or how to update each record with the correct total price? — user1726343
– user1726343, Commented Mar 4, 2013 at 3:00

John Woo · Accepted Answer · 2013-03-04 03:07:54Z

2

The reason why I used LEFT JOIN on the following query is because I assumed that some orders may only contain drinks or foods.

UPDATE  order_table a
        LEFT JOIN food b
            ON a.foodid = b.foodID
        LEFT JOIN drink c
            ON a.drinkID = c.drinkID
SET     a.total_price = (a.food_count * COALESCE(b.price, 0) +
                        a.drink_count * COALESCE(c.price, 0))

SQLFiddle Demo

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

edited Mar 4, 2013 at 3:07

answered Mar 4, 2013 at 3:02

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

sgeddes · Accepted Answer · 2013-03-04 03:01:15Z

0

Something like this should be close:

SELECT COALESCE(F.Price,0)*OT.Food_Count+COALESCE(D.Price,0)*OT.Drink_Count Total_price 
FROM Order_Table OT
    LEFT JOIN Food F ON OT.FoodId = F.FoodId
    LEFT JOIN Drink D ON OT.DrinkId = D.DrinkId

And to actually update that column:

UPDATE Order_Table OT
    LEFT JOIN Food F ON OT.FoodId = F.FoodId
    LEFT JOIN Drink D ON OT.DrinkId = D.DrinkId
SET OT.Total_Price =  COALESCE(F.Price,0)*OT.Food_Count+COALESCE(D.Price,0)*OT.Drink_Count

answered Mar 4, 2013 at 3:01

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

Update data in Multiple table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related