MySQL divide prices by currency rate from another table -

i have 2 tables, orders , order_detail.

orders

        id_order | currency | conversion_rate --------------------------------------------         1             1             1         2             1             1         3             7             8.523856         4             1             1         5             1             1         6             7             8.457893         7             1             1 

order_detail

        id_order | id_order_detail | price --------------------------------------------         1             1             100         1             2             150         2             3             150         3             4             2500         3             5             2100         4             6             160         5             7             190         6             8             2300         6             9             1500         7             10             125 

i need divide prices in order_detail table conversion_rate orders table corresponding order_id currency not 1 , rate not 1 (basically convert orders default currency, 1).

so desired outcome be:

order_detail

        id_order | id_order_detail | price --------------------------------------------         1             1             100         1             2             150         2             3             150         3             4             293.29449         3             5             246.367371         4             6             160         5             7             190         6             8             271.93533         6             9             177.349134         7             10             125 

i did query:

update order_detail set price = price/orders.conversion_rate orders.id_order = order_detail.id_order        , orders.conversion_rate != 1        , orders.currency != 1;  

i stuck here because gives errors unknown column orders.conversion_rate, although right there.

please me complete query.

you missing correlate both table in query cannot grab data order table. used left join in inner join query update orders_detail table

update order_detail left join orders b on a.id_order = b.id_order set a.price = a.price/b.conversion_rate a.id_order = b.id_order , b.conversion_rate != 1 , b.currency != 1;