November 5, 2019 at 2:54 pm
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.
November 5, 2019 at 3:41 pm
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
November 5, 2019 at 3:44 pm
Oh, I see. Haha, just me trying to be clever
Thank you for your help Mike. I'll go with the simple option.
November 5, 2019 at 8:21 pm
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
November 6, 2019 at 3:05 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply