Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Can't find the right sytax.

Posted on 2011-03-17
Medium Priority
287 Views
I would like to use a query to update some fields using the current month as a condition.
I can't get the notation right (I'm rather new to sql) but I guess it should look something like this:

SELECT CASE DATEPART(mm,getdate()) WHEN 1 THEN UPDATE empire SET darkforce='evil' where person='vader' ELSE
SELECT CASE DATEPART(mm,getdate()) WHEN 2 THEN UPDATE empire SET darkforce='evil' where person='palpetine' ELSE
SELECT CASE DATEPART(mm,getdate()) WHEN 3 THEN UPDATE empire SET transport='falcon' where person='hansolo'

Could anyone let me know what the right syntax should be?

Best regards,
Clyde.
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 3

Expert Comment

ID: 35156175
what are you trying to do here ? update has nothing to do with select.
0

LVL 32

Assisted Solution

Ephraim Wangoya earned 664 total points
ID: 35156229

try this way

update empire
set darkforce = case
when  DATEPART(mm,getdate()) = 1 then
when  DATEPART(mm,getdate()) = 2 then
'palpetine'
end
where DATEPART(mm,getdate()) IN (1, 2)
0

LVL 18

Accepted Solution

Cluskitt earned 668 total points
ID: 35156263
UPDATE table
SET CASE
WHEN MONTH(getdate()) IN (1,2) THEN darkforce
WHEN MONTH(getdate())=3 THEN transport
END=CASE
WHEN MONTH(getdate()) IN (1,2) THEN 'evil'
WHEN MONTH(getdate())=3 THEN 'falcon'
END
WHERE person=CASE
WHEN MONTH(getdate())=2 THEN 'palpetine'
WHEN MONTH(getdate())=3 THEN 'hansolo'
END

0

LVL 8

Assisted Solution

pdd1lan earned 668 total points
ID: 35156275
UPDATE empire SET
CASE DATEPART(mm,getdate()) WHEN 1 then darkforce='evil' where person='vader'
CASE DATEPART(mm,getdate()) WHEN 2 thendarkforce='evil' where person='palpetine'
CASE DATEPART(mm,getdate()) WHEN 3 then transport='falcon'  where person='hansolo'
else
darkforce='something'
END
0

Author Comment

ID: 35156313
Sorry my explanation of the issue was rather poor... :-)
I would like to update a field on a certain month.

If the month is january then field x should be updated with value a
If the month is february then field x should be updated with value b
If the month is march then field x should be updated with value c etc...
0

LVL 18

Expert Comment

ID: 35156385
You should test the solutions before accepting them. I don't think pdd1lan's solution will work. You can't place conditions or assignments in a case. Only expressions.
0

## Featured Post

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signalâ€¦
###### Suggested Courses
Course of the Month11 days, 19 hours left to enroll