CODEWITHSUNDEEP
sql
Nugeswale
Nugeswale

Reputation: 63

Set values using a case statement

Table Rate:

Fees | Region  | Rate
-----+---------+---------
 A1  | Intra   | 0.00015   
 A2  | Intra   | 0.000325   
 A3  | Inter   | 0.000025   
 A4  | Inter   | 0.015

Table Amount:

Region | Amount | A1 | A2 | A3 | A4
-------+--------+----+----+----+----
Intra  | $10    |  ? |  ? |  ? |  ?
Intra  | $11    |  ? |  ? |  ? |  ?
Inter  | $12    |  ? |  ? |  ? |  ?
Inter  | $13    |  ? |  ? |  ? |  ?

Any help will do, I am struggling to write the case statement that will update table using the different rates in table 1.

I have tried:

Update a
set A.A1 = Amount * R.Rate
from dbo.Amount as A 
inner join dbo.Rate R where R.Region = A.region

I want the rate applied to be the Rate A1 and Region to be inter/Intra depending on the Region in Amount table

Update a
set A.A2 = Amount * R.Rate
from dbo.Amount as A 
inner join dbo.Rate R where R.Region = A.region

I want the rate applied to be the Rate A2 and Region to be inter/Intra depending on the Region in Amount table) etc

Please help

Upvotes: 0

Views: 50

Answers (2)

shawnt00
shawnt00

Reputation: 17915

I gather this is how your tables are related.

update Amount
set
    A1 = Amount * (select Rate from Rate where Region = Amount.Region and Fees = 'A1'),
    A2 = Amount * (select Rate from Rate where Region = Amount.Region and Fees = 'A2'),
    A3 = Amount * (select Rate from Rate where Region = Amount.Region and Fees = 'A3'),
    A4 = Amount * (select Rate from Rate where Region = Amount.Region and Fees = 'A4')

One of the advantages of using subqueries is that you'll get an error if you return more than one row. There are reasons to avoid the update ... from ... syntax. Based on the sample data it appears you're going to have nulls so you'll want to use outer join rather than inner join if you do choose to do it that way.

Upvotes: 1

Kostya
Kostya

Reputation: 1605

here is one way of doing it 

Update a
set 
A.A1 = Amount* R1.Rate
,A.A2 = Amount* R2.Rate
,A.A3 = Amount* R3.Rate
,A.A4 = Amount* R4.Rate
from dbo.Amount as A 
inner join dbo.Rate R1 
on R1.Region = A.region and r1.fees = 'A1'
inner join dbo.Rate R2 
on R2.Region = A.region and r2.fees = 'A2'
inner join dbo.Rate R3 
on R3.Region = A.region and r3.fees = 'A3'
inner join dbo.Rate R4 
on R4.Region = A.region and r4.fees = 'A4'

Upvotes: 1

Related Questions