CODEWITHSUNDEEP
sql
Lion B
Lion B

Reputation: 21

Cannot perform UPDATE with CASE statement

I'm trying to update a temp table based on the value of a column in the table. My CASE statement doesn't seem to work. Any ideas? Help much appreciated.

Here's where I create the temp table. The columns contain values.

IF OBJECT_ID('tempdb..#Accounts') IS NOT NULL DROP TABLE #Accounts;
Create Table #Accounts
(
    FA_rows bigint,
    PR_rows bigint,
    NewFARate1 decimal(10,5),
    NewFARate2 decimal(10,5),
    NewFARate3 decimal(10,5),
    NewFARate4 decimal(10,5),
    NewFARate5 decimal(10,5),
    PctRate1 decimal (10,5),
    PctRate2 decimal (10,5),
    PctRate3 decimal (10,5),
    PctRate4 decimal (10,5),
...PctRate10 decimal (10,5)
)

Here's the attempted update (I've added more details):

UPDATE #Accounts

--when fa_rows is 2 I need to do this
    SET NewFARate1 = CASE FA_rows WHEN 2 THEN PctRate12 - PctRate7 END,
    NewFARate2 = CASE FA_rows WHEN 2 THEN PctRate3 - PctRate8 END,

---when fa_rows is 3 I need to do this
    SET NewFARate1 = CASE FA_rows WHEN 3 THEN PctRate12 - PctRate7 END,
    NewFARate2 = CASE FA_rows WHEN 3 THEN PctRate3 - PctRate8 END,
    NewFARate3 = CASE FA_rows WHEN 3 THEN PctRate3 - PctRate8 END

--when fa_rows is 4 I need to do this
    SET NewFARate1 = CASE WHEN 4 THEN PctRate2 - PctRate7 END,
    NewFARate2 = CASE WHEN 4 THEN PctRate3 - PctRate8 END,
    NewFARate3 = CASE WHEN 4 THEN PctRate4 - PctRate9 END,
    NewFARate4 = CASE WHEN 4 THEN PctRate5 - PctRate10 END                    
    WHERE FA_rows = PR_rows

This code is obviously not working. The preprocessor doen't like the multiple SET commands.

Upvotes: 1

Views: 64

Answers (2)

Siyual
Siyual

Reputation: 16917

CASE is an expression, not a statement. It cannot be used as a logic control in SQL. You can however use the following to give you your results:

Update  #Accounts
Set     NewFARate1 = Case 
                        When fa_rows In (2, 3)
                            Then PctRate2 - PctRate7
                        Else NewFARate1 End,
        NewFARate2 = Case
                        When fa_rows In (2, 3) 
                            Then PctRate3 - PctRate8
                        Else NewFARate2 End,
        NewFARate3 = Case
                        When fa_rows In (3) 
                            Then PctRate4 - PctRate9
                        Else NewFARate3 End
Where   FA_rows = PR_rows

The above uses separate CASE expressions to determine what to update the values of the columns to. If it isn't in the values supplied, it will set the value of the column to itself - thus, not updating the column.

Upvotes: 2

Joe Phillips
Joe Phillips

Reputation: 51130

Think of a CASE as more of a function that returns a value rather than a conditional.

UPDATE #Accounts
SET NewFARate1 = CASE fa_rows WHEN 2 THEN PctRate2 - PctRate7 WHEN 3 THEN PctRate2 - PctRate7 END,
    NewFARate2 = CASE fa_rows WHEN 2 THEN ... etc
...
END
WHERE FA_rows = PR_rows

Upvotes: 2

Related Questions