Update Records using SQL CASE Statement for Conditional Logic

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.

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

Previous SQL Exercise: Insert from another Table.
Next SQL Exercise: Delete All Records.