Solved

How to use multiple conditions using when then?

Posted on 2011-03-23
5
583 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Clyde_Radcliffe
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 35197465
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 35197471
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
 

Author Comment

by:Clyde_Radcliffe
ID: 35197491
@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
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 35197521
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
 

Author Comment

by:Clyde_Radcliffe
ID: 35198393
@Qlemo: Thanks! I didn't realize that I should use the ELSE.
Everything works like a charm now!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question