CASE WHEN EXISTS UPDATE

Question

Post reply

CASE WHEN EXISTS UPDATE

paul 69259

Hall of Fame

Points: 3068
More actions
November 5, 2019 at 2:54 pm

#3695151
Hi All,
I'm struggling a bit with some syntax for a CASE WHEN statement. I don't even know if it's possible to use it the way I'm trying to use it.
What I need to do is update some values in my "Drivers" table depending on some results from a CASE WHEN statement.
The only examples I've been able to find online deal with only updating 1 value. I need to update a possible 8 different values. When I say update, what I mean is of they exist I need to set them to 0.
I know I've only got 2 values in my code snippet below but I'm just after bit of guidance on the syntax or is it even possible to use CASE WHEN in this way?
What I'm trying to do in the below statement is set the driver rateid to zero if there is a rateid in the driver table = 40 if not, check to see if there is a pickup_rate_id in the driver table =40, if there is set that to zero and so on.... for the rest of my fields.
```
update driver set
         case when exists
                    (select rateid from driver where rateid = 40) then 
                    (rateid = 0 where rateid = 40)
          ELSE
          case when exists 
                    (select pickup_rateid from driver where pickup_rateid = 40 ) then 
                    (set pickup_rateid = 0 where pickup_rateid = 40)
          end end
```
Thanks in advance
Paul.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

it wasn't too clear from your description what you wanted

if you want to update each row based on it's own values then

update driver set rateid= case when rateid=40 then 0 else rateid end,

pickup_rateid=case when pickup_rateid=40 then 0 end

from driver

this might not perform well as it will update every record, so it might be better writing a simple update statement for each field

but to be honest it might be better to just do

update driver set rateid=0 where rateid=40

update pick_rateid=0 where pcickup_rateid=40

MVDBA

paul 69259 Hall of Fame Points: 3068 More actions · Answer 2

Oh, I see. Haha, just me trying to be clever

Thank you for your help Mike. I'll go with the simple option.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

It helps to remember that, in SQL, CASE is an expression, not a flow control statement. All CASE does is use if/then/else logic to produce a single value.

Your original logic looks as if you wanted to only update [pickup_rateid] when the [rateid] column was NOT being updated. Not sure if that was the intent, but if so, you can still get the job done with one update statement.

UPDATE Driver
SET RateID = CASE when RateID = 40 then 0 else RateID end        
   ,Pickup_RateID = CASE when RateID <> 40 and Pickup_RateID = 40 then 0 else Pickup_RateID end
WHERE RateID = 40 or Pickup_RateID = 40

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

paul 69259 Hall of Fame Points: 3068 More actions · Answer 4

Thank you. My intention was if any of the fields were = 40 then update them to zero. I used the single update statements as suggested by Mike in the end. That's a handy bit of code though, thank you