CODEWITHSUNDEEP
sqlif-statementjoin
Rashmi Kini
Rashmi Kini

Reputation: 19

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

Request your help in writing update query with joining 2 tables and an if condition.

Converting the amount to USD here. There are 2 tables. A - with amount and original currency, B- with Exchange rate, currency, and type. Exch_rate value should be fetched with below conditions and where b.[Rate Type]='Actual'. In case if a particular currency does not have corresponding row with b.[Rate Type]='Actual', then it should consider exch_rate of row with value b.[Rate Type]='NA'

This is what i have got so far. This query is able to only update the items which has corresponding row in table B where [Rate Type]='Actual'. I am not sure how can I check if corresponding rows are there or not and update accordingly

update a
    set [USD_AMT]=a.[Amt] *  b.[Exch Rate]
    from [dbo].a
    inner join [dbo].b
on
    a.[Currency]=b.[From Cur]
    and month(a.month)=month(b.[Eff Date])

where
    b.[To Cur]='USD' and
    b.[Rate Type]='Actual'

Table A:

Amt | Currency | Month | USD_AMT

100 | GBP | Jan

200 | ISD | Feb

Table B:

From cur | To 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.

Upvotes: 0

Views: 1209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In case if a particular currency does not have corresponding row with b.[Rate Type]='Actual', then it should consider exch_rate of row with value b.[Rate Type]='NA'.

The standard approach uses two left joins, one for the preferred value and one for the default value:

update a
    set [USD_AMT] = a.[Amt] *  coalesce(b.[Exch Rate], bdef.[Exch Rate])
    from [dbo].a left join
         [dbo].b
         on a.[Currency] = b.[From Cur] and
            month(a.month) = month(b.[Eff Date]) and
            b.[To Cur] = 'USD' and
            b.[Rate Type] = 'Actual' left join
         [dbo].b bdef
         on a.[Currency] = bdef.[From Cur] and
            month(a.month) = month(bdef.[Eff Date]) and
            -- b.[To Cur] = 'USD' and  -- I don't know if this is needed
            b.[Rate Type] = 'N/A';

Upvotes: 1

Related Questions