sql for product price update from exchange rate from multiple table

Question

I have two tables "curreny" and "product_price". Some goods imported so we need to keep their prices updated as the currency rates changes.

"curreny" table keeps updated currency rates for various currencies.

"product_price" table keeps fields "main_price","foreign_price","currency_iso","base_currency"

Curreny Table (daily updated)

c_iso      rate
---------------
USD       3.0257
EUR       3.3547

Product_price Table

 id       def     main_price  foreign_price  currency_iso  base_currency
 1     Product1      30.2570       10            USD           1        
 2     Product2      50            14.904        EUR           0      
 3     Product3      67.094        20            EUR           1   

I need to update all product price according to base_currency.

For example for product1 base_currency is 1, which means USD is the real price of the product. Thus according to the new rates on table "currency" the foeign_price of the product should multiply by USD rate.

 main_price = foreign_price*rate

for product2 base_currency is 0, which means main_price is the real price of the product. Thus according to the new rates on table "currency" the foeign_price of the product should be dived by USD rate.

 foreign_price = main_price/rate.

Answer 1

After trying multiple settings of if/case statements, i found a working code:

update product_price as p 
inner join currency as c  on(c.c_iso = p.currency_iso)   
set p.main_price = case p.base_currency when true then
p.foreign_price*c.rate when false then p.main_price
end,
p.foreign_price = case p.base_currency when false then
p.main_price/c.rate when true then p.foreign_price 
end;

Answer 2

I think this is the update you need:

update product_price p join
       currency c
       on c.c_iso = p.currency_iso
    set main_price = (case when p.base_currency = 1
                           then p.foreign_price * c.rate
                           else p.foreign_price / c.rate
                      end)
    where p.base_currency in (0, 1);

EDIT:

Based on the comment:

update product_price p join
       currency c
       on c.c_iso = p.currency_iso
    set main_price = (case when p.base_currency = 1
                           then p.foreign_price * c.rate
                           else main_price
                      end),
        foreign_price = (case when p.base_currency = 0
                              then p.foreign_price / c.rate
                              else foreign_price
                      end)
    where p.base_currency in (0, 1);