Update Records using SQL CASE Statement for Conditional Logic

Last update on September 20 2025 13:31:09 (UTC/GMT +8 hours)

Update with CASE Statement

Write a SQL query to update records in a table using conditional logic with a CASE statement.

Solution:

-- Update salaries based on age: increase by 5000 if age > 30, otherwise increase by 2000.
UPDATE Employees -- Specify the table to update.
SET Salary = CASE 
    WHEN Age > 30 THEN Salary + 5000 -- Increase by 5000 if age > 30.
    ELSE Salary + 2000 -- Otherwise, increase by 2000.
END;

Explanation:

Purpose of the Query :

The goal is to update the Salary column in the Employees table based on the employee's age.
This demonstrates how to use a CASE statement within an UPDATE query to apply different updates depending on specific conditions.

Key Components :

UPDATE Employees : Specifies the table where the update will occur.
SET Salary = CASE ... END : Uses a CASE statement to conditionally calculate the new salary value.

WHEN Age > 30 THEN Salary + 5000 : Increases the salary by 5,000 for employees older than 30.
ELSE Salary + 2000 : Increases the salary by 2,000 for all other employees.

Why use a CASE Statement? :

A CASE statement allows you to apply different updates to rows based on specific conditions, making it ideal for scenarios where updates are not uniform across all records.
For example, if you want to give raises based on age groups, a CASE statement provides the flexibility to handle multiple conditions in a single query.

Real-World Application :

For example, in a company database, you might use this query to implement a policy where employees over the age of 30 receive a larger raise (5,000) compared to younger employees (2,000).

Additional Notes:

CASE statement is a powerful tool for applying complex conditions in SQL queries.
Scenarios where using a CASE statement is appropriate, such as:

Applying tiered pricing or discounts based on customer attributes.
Updating data based on ranges, categories, or classifications.

Important Considerations :

Ensure that the conditions in the CASE statement are mutually exclusive and cover all possible cases to avoid unexpected results.
Test the query on a small dataset or in a development environment before applying it to production data.

For more Practice: Solve these Related Problems:

Write a SQL query to update the `status` column in the `orders` table using a CASE statement to set 'Shipped' for orders older than 7 days and 'Processing' otherwise.
Write a SQL query to update the `grade` column in the `students` table using a CASE statement to assign grades based on their scores.
Write a SQL query to update the `discount` column in the `products` table using a CASE statement to apply discounts based on product categories.
Write a SQL query to update the `priority` column in the `tasks` table using a CASE statement to assign priorities based on task deadlines.

Go to:

PREV : Insert from another Table.
NEXT : Delete All Records.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.