CODEWITHSUNDEEP
sqlsql-server
Saravanan
Saravanan

Reputation: 7844

Using a conditional UPDATE statement in SQL

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.

Upvotes: 80

Views: 150843

Answers (3)

Usha Rani
Usha Rani

Reputation: 1

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:

  1. CASE expression in SET: The CASE checks each row's age and applies the appropriate value.
  2. 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.

Upvotes: 0

JNK
JNK

Reputation: 65217

I think what you want is:

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

Upvotes: 152

EBarr
EBarr

Reputation: 12026

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

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

Upvotes: 37

Related Questions