Solved

How to use multiple conditions using when then?

Posted on 2011-03-23
5
559 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 68

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 68

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now