How to use multiple conditions using when then?

I am trying to update a field.
The field should be updated only if the current month is x and field_04 contains value y.
When I use a single condition the field gets updated just fine.
but I can't seem to get it to function when I add another condition.

This works:

update Items
set Field_04 = case
      when  DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
      when  DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
       end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'

This doesn't work:

update Items
set Field_04 = case
      when  DATEPART(mm,getdate()) = 1 then '3500'
      when  DATEPART(mm,getdate()) = 2 then '4500'
       end
from Items
where DATEPART(mm,getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) and Condition='A'

I guess it has something to do with the when then statement.
Maybe it cannot contain multiple conditions or maybe I'm just using the wrong syntax?

Thanks in advance!
Clyde.
Clyde_RadcliffeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Is Field_04 really varchar? You should always check with a control SELECT first, containing some identifying column, the columns you check for, and the expression you need to generate:

select field_04, date_part(mm, getdate()), case
      when  DATEPART(mm,getdate()) = 1 and Field_04<'3500' then '3500'
      when  DATEPART(mm,getdate()) = 2 and Field_04<'4500' then '4500'
      else field_04
       end as expr
from items where Condition = 'A'

Further you should always provide an else - else the result is NULL if no condition is met.
0
 
tigin44Commented:
try this

update Items
set Field_04 = case
      when  MONTH(getdate()) = 1 then '3500'
      when  MONTH(getdate()) = 2 then '4500'
       end
from Items
where MONTH(getdate()) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
  and Condition='A'
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You have mixed up the not working and the working solution.
No, I don't see why that should not work. You can concat conditions in the WHEN clause, as you did.
But your where condition does not make much sense - the datepart of month  will always be in the range of 1 to 12, so that whole part is superfluous.
0
 
Clyde_RadcliffeAuthor Commented:
@Qlemo: I have spend way to many hours after the screen. Sorry for the mixup! :-)
Somehow it won't change the value when I add an extra condition. :-S
0
 
Clyde_RadcliffeAuthor Commented:
@Qlemo: Thanks! I didn't realize that I should use the ELSE.
Everything works like a charm now!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.