1

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.

Improve this question
4
  • It's not a CASE statement, it's an expression. CASE cannot be used as a control flow in SQL. Commented Feb 13, 2017 at 21:03
  • This won't work. CASE statements are not IF/ELSE logic. Commented Feb 13, 2017 at 21:03
  • 1
    Why would someone downvote this? It seems like a perfectly legitimate question and misunderstanding of how something works. Commented Feb 13, 2017 at 21:05
  • @LionB Why do you keep trying multiple sets? You can't do that. In my example I have multiple "when...." checks. You have not done this in your code which is why it isn't working Commented Feb 13, 2017 at 23:56

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi Joe, thanks for this solution, but I think I didn't adequately describe the problem. I need to update columns NewFARate1, NewFARate2...etc for each record based on the value of FA_rows. So here's what I'm trying to do but the sql I'm using is obviously not working:
@LionB Just add an ELSE to the case statement that sets the FA stuff to itself if you don't want to update it
Thanks but problem still not solved. Please look at the problem again. I've updated it. Also, ELSE is optional and should not affect the code if omitted correct?
@LionB You're not understanding my example. It answers your question quite clearly. For all of your NewFARate1 cases you have to do them all in the one case statement. There is no good way around that
I do understand you. It's my fault for not being completely clear. Here's what I'm trying to do. Each expression in the CASE statement (Pctrate(n) - PctRate(n)) must be assigned to a new column (NewFARate(n)). Thus NewFARate1 = (PctRate2 - PctRate7), NewFARate2 = (PctRate3 - PctRate8), NewFARate3 = (PctRate 4 - PctRate9), etc. And this needs to happen for each row in the table. Your code example doesn't seem to capture this. But thanks anyway for your help.
|
2

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.

Improve this answer

3 Comments

I think I didn't adequately describe the problem. I need to update columns NewFARate1, NewFARate2...etc for each record based on the value of FA_rows. So here's what I'm trying to do. If fa-rows is 2 then I want to update NewFARate1 & NewFARate2. If fa_rows is 3 then I want to update NewFARate1, NewFARate2 and NewFARate3. Thus I will always update NewFARate1 though N for each row in the table. I hope that's clear.
@LionB Unless I'm missing something, my solution will do exactly that.
Siyual. You're absolutely right. This worked! I altered my query a little based on your logic and got this:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.