Using a conditional UPDATE statement in SQL

Question

I would like to have an UPDATE statement like this:

 SELECT *
 FROM Employee
 WHERE age = CASE 
 WHEN (age < 20) THEN age=15
 WHEN (age > 20) THEN age= 20

Is this not possible in SQL Server / MySQL? I do not want to use the stored procedures or other things.

Suggest me a suitable way around this problem.

JNK · Accepted Answer · 2011-05-23 13:28:02Z

153

I think what you want is:

UPDATE EMPLOYEE
SET age =
CASE WHEN AGE < 20 THEN 15
ELSE 20 END

answered May 23, 2011 at 13:28

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saravanan Over a year ago

Thanks.. It works good. I am working with it on MS SQL Server and MySQL Server too.

UPDATE testingtable SET dob =  CASE WHEN DATEDIFF(CURRENT_TIMESTAMP,ADDDATE(dob, INTERVAL -2 day)) > 1 THEN ADDDATE(dob,INTERVAL -2 DAY) ELSE CURRENT_TIMESTAMP END WHERE username='bob';

. Thanks for your reply.

sonyisda1 Over a year ago

If looking to leave value if does not meet condition then set the value to the fieldname. For example above the ELSE 20 END would be ELSE age END

EBarr · Accepted Answer · 2011-05-23 13:27:37Z

37

You can use a case statement in an update as follows...

UPDATE Employee 
SET Age = CASE WHEN (age < 20) THEN 15
              ELSE 20 END

answered May 23, 2011 at 13:27

EBarr

12.1k7 gold badges68 silver badges87 bronze badges

4 Comments

EBarr Over a year ago

ha, we must have just crossed paths typing out answers -- i didn't see any others when i submit:-D

DForck42 Over a year ago

i like how the only different from op's code and yours is the update instead of select and you added an else clause

EBarr Over a year ago

and made the assignment a CASE instead having it in the where!

TTT Over a year ago

@EBarr - the reason you didn't see any other answers when you submitted is because you were first...

Usha Rani · Accepted Answer · 2024-11-07 09:27:05Z

In SQL Server and MySQL, you can use an UPDATE statement with a CASE expression directly in the SET clause to conditionally update values based on certain conditions. Here’s how you can structure the query:

UPDATE Employee SET age = CASE 
         WHEN age < 20 THEN 15
         WHEN age > 20 THEN 20
         ELSE age  -- Keep the current age if it's exactly 20
      END WHERE age <> 20;  --Optional: Only update rows where age is not already 20

Explanation:

CASE expression in SET: The CASE checks each row's age and applies the appropriate value.
WHERE clause: Using WHERE age <> 20 (optional) prevents unnecessary updates for rows where age is already 20.

This query will update age to 15 if it’s less than 20, to 20 if it’s greater than 20, and leave age unchanged if it’s exactly 20.

This method avoids the need for stored procedures and is compatible with both SQL Server and MySQL.

Collectives™ on Stack Overflow

Using a conditional UPDATE statement in SQL

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related