Update...Case...Between

[Mich]

Something tells me this is going to be an easy one. I hope it is because I have spent the past two hours trying to figure it out.

I'm trying to update a table based on the value in the RefNum column. I thought the best way to do this was a simple CASE statement, but I've been running into a wall.

Here is basically what I have,

update dbo.sessionsales
set Unit = (Case RefNum
when 154 then 'Pour'
when 181 then 'Pour'
-- when between 200 and 300 then 'Glass'
-- when RefNum between 200 and 300 then 'Glass'
-- when > 200 then 'Glass'
-- when RefNum > 200 then 'Glass'
-- when (Refnum between 200 and 300) then 'Glass'
else 'other'
end)

The commented lines are what is giving me problems (I only attempt one at a time). Syntax wise I have tried everything I know to try. This statement works fine as it is, but when I try to use any operator (=, <, >, between, etc.) I get the error "Incorrect syntax near 'INSERT OPERATOR HERE'.

RefNum is smallint.

Any clues?

-If it ain't broke, break it and make it better.

 

[RyanEK]

Hi,

Try this approach...

update dbo.sessions
set unit = 
  case when refnum in (154,181) then 'Pour'	
  when refnum between 200 and 300 then 'Glass'
  else 'Other' end

Ryan

 

[Mich]

Holy hell. I tried a hundred different syntax permutations. What you suggested worked...what was I doing wrong?

The only thing I can see is the CASE statement. I declared "CASE Refnum" and you didn't. Is that it?

-If it ain't broke, break it and make it better.

 

[RyanEK]

Hehe yeah. If you want to place field conditions in a case statement, the above syntax is the way to go.

One of those days eh?