How to Update Multiple Records in SQL Based on a Condition

Update Multiple Records

Write a SQL query to update multiple records in a table based on a specific condition.

Solution:

-- Increase the salary of all employees aged over 30 by 10%.
UPDATE Employees -- Specify the table to update.
SET Salary = Salary * 1.10 -- Increase the salary by 10%.
WHERE Age > 30; -- Apply the update to employees older than 30.

Explanation:

1. Purpose of the Query :

1. The goal is to increase the Salary of all employees in the Employees table who are older than 30 by 10%.
2. This demonstrates how to use the UPDATE statement to modify multiple records simultaneously based on a condition.

2. Key Components :

1. UPDATE Employees : Specifies the table where the update will occur.
2. SET Salary = Salary * 1.10 : Uses an arithmetic expression to calculate the new salary by increasing it by 10%.
3. WHERE Age > 30 : Filters the rows to apply the update only to employees whose age is greater than 30.

3. Why use UPDATE with a Condition? :

1. Updating multiple records based on a condition is useful when you need to apply changes to a subset of rows that meet specific criteria.
2. For example, if a company decides to give a 10% raise to all employees over the age of 30, this query ensures that only those employees receive the adjustment.

4. Real-World Application :

1. For example, in a company database, you might use this query to implement a policy where employees over the age of 30 receive a 10% salary increase as part of a retention or recognition program.

Additional Notes:

• Updating multiple records in one query is more efficient than updating them individually.
• Scenarios where updating multiple records is appropriate, such as:
• Implementing company-wide policies (e.g., raises, bonuses).
• Correcting errors in a subset of data (e.g., updating outdated information).
• Important Considerations :
• Ensure that the WHERE clause accurately filters the intended rows to avoid unintended updates.
• Test the query on a small dataset or in a development environment before applying it to production data.

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

Previous SQL Exercise: Update Single Record
Next SQL Exercise: Delete Single Record.