[FIXED] How to update table using information from multiple tables

Issue

There are 3 tables in my database.

table1:

+---------+----------+---------+-----+
|  name   | order_no | comment | sum |
+---------+----------+---------+-----+
| John    |        1 |         |     |
| Dimitri |        3 |         |     |
| Peter   |        6 |         |     |
+---------+----------+---------+-----+

table2:

+-------------+------------+-------+
| fk_order_no | fk_film_no | count |
+-------------+------------+-------+
|           1 |         10 |    20 |
|           1 |         15 |    15 |
|           3 |         15 |    30 |
|           3 |         16 |    16 |
|           6 |         69 |    37 |
+-------------+------------+-------+

table3:

+----------------------+---------+-------+
|         name         | film_no | price |
+----------------------+---------+-------+
| Pacific Rim          |      10 |  16.5 |
| Pacific Rim Uprising |      15 |  13.3 |
| Pacifier             |      16 | 11.01 |
| Package              |      69 |  0.34 |
| Pagemaster           |      22 |  0.14 |
| Painted Veil         |      66 |  0.17 |
+----------------------+---------+-------+

I want to update sum column of table1. This is how sum is calculated

sum (order_no = 1) = 20 (count from table2) * 16.5 (price from table3) + 15 (count from table2) * 13.3 (price from table 3)

sum(order_no = 3 )= 30 * 13.3 + 16 * 11.01

sum(order_no=6) = 37 * 0.34

table1 should look like this:

+---------+----------+---------+--------+
|  name   | order_no | comment |  sum   |
+---------+----------+---------+--------+
| John    |        1 |         |  529.5 |
| Dimitri |        3 |         | 575.16 |
| Peter   |        6 |         |  12.58 |
+---------+----------+---------+--------+

Solution

Hmmm . . . the data for the calculation comes from a join and aggregation:

select t2.fk_order_no, sum(t2.count * t3.price)
from table2 t2 join
     table3 t3
     on t2.fk_film_no = t3.film_no
group by t2.fk_order_no;

You can then incorporate this into an update using join:

update table1 t1 join
       (select t2.fk_order_no, sum(t2.count * t3.price) as total_price
        from table2 t2 join
             table3 t3
             on t2.fk_film_no = t3.film_no
        group by t2.fk_order_no
       ) t23
       on t1.order_no = t23.fk_order_no
    set t1.sum = t23.total_price;

Answered By - Gordon Linoff