if statement - SQL query - To update a column with if condition and join -

request in writing update query joining 2 tables , if condition.

converting amount usd here. there 2 tables. - amount , original currency, b- exchange rate, currency, , type. exch_rate value should fetched below conditions , b.[rate type]='actual'. in case if particular currency not have corresponding row b.[rate type]='actual', should consider exch_rate of row value b.[rate type]='na'

this have got far. query able update items has corresponding row in table b [rate type]='actual'. not sure how can check if corresponding rows there or not , update accordingly

update     set [usd_amt]=a.[amt] *  b.[exch rate]     [dbo].a     inner join [dbo].b on     a.[currency]=b.[from cur]     , month(a.month)=month(b.[eff date])      b.[to cur]='usd' ,     b.[rate type]='actual'  

table a:

amt | currency | month | usd_amt

100 | gbp | jan

200 | isd | feb

table b:

from cur | cur| rate type | month | exch_rate

gbp usd actual jan 0.16

gbp usd na jan 0.18

isd usd na feb 65

please help.

in case if particular currency not have corresponding row b.[rate type]='actual', should consider exch_rate of row value b.[rate type]='na'.

the standard approach uses 2 left joins, 1 preferred value , 1 default value:

update     set [usd_amt] = a.[amt] *  coalesce(b.[exch rate], bdef.[exch rate])     [dbo].a left join          [dbo].b          on a.[currency] = b.[from cur] ,             month(a.month) = month(b.[eff date]) ,             b.[to cur] = 'usd' ,             b.[rate type] = 'actual' left join          [dbo].b bdef          on a.[currency] = bdef.[from cur] ,             month(a.month) = month(bdef.[eff date]) ,             -- b.[to cur] = 'usd' ,  -- don't know if needed             b.[rate type] = 'n/a';