Using Subqueries in the WHERE Clause of UPDATE Statements in SQL

A subquery is a query that is used inside another SQL query. It can be used to return a single value or a set of values that will be used by the outer query. Subqueries in the WHERE clause are particularly useful when we need to filter records based on conditions that involve other tables or more complex criteria. Basic syntax for using a subquery within the WHERE clause of an UPDATE statement is given below:

Syntax:

UPDATE TableName
SET column_name = new_value
WHERE column_name IN (SELECT column_name FROM another_table or same_table WHERE condition);

Here,

• TableName: The table that contains the records to be updated.
• column_name: The column to be updated in the target table.
• new_value: The new value to assign to the column.
• The subquery: A nested query that selects values used to filter which rows to update.

Examples of Using Subqueries in the WHERE Clause of SQL UPDATE

To understand How to perform subqueries in the WHERE Clause of UPDATE statement in SQL, We will consider two table called Employees, Departments on which we will perform various operations and queries. as shown below:

1. Employees Table

2. Department Table

Example 1: Update Records Based on a Condition from Another Table

Suppose we want to update the salary of employees in the Sales department to match the average salary defined in the Departments table for that department.

Query:

UPDATE Employees
SET Salary = (SELECT AvgSalary FROM Departments WHERE Department = 'Sales')
WHERE Department = 'Sales';

Output

Explanation:

• The subquery (SELECT AvgSalary FROM Departments WHERE Department = 'Sales') finds the average salary for the "Sales" department.
• The UPDATE statement sets the Salary of employees in the Sales department to this average salary.
• The WHERE clause ensures that only employees in the "Sales" department are updated.

Example 2: Increase the Salary of Employees

Suppose we want to increase the salary of employees by 10% in their respective departments for those who earn less than the average salary of their department. We can write the following query using a subquery in the WHERE clause:

Query:

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary < (SELECT AVG(Salary) FROM Employees WHERE Department = Employees.Department);

Output

Explanation:

• The subquery (SELECT AVG(Salary) FROM Employees WHERE Department = Employees.Department) calculates the average salary for each department.
• The UPDATE statement increases the salary by 10% for employees who earn less than the average salary in their department.

Example 3: Update with a Subquery to Modify Data Based on Multiple Conditions

Suppose we want to update the Salary of employees who have a salary lower than the average salary of their respective departments. This will involve a correlated subquery because the inner query will reference the DepartmentID from the outer query.

Query:

UPDATE Employees
SET Salary = Salary * 1.15
WHERE Salary < (SELECT AVG(Salary) 
 FROM Employees 
 WHERE Employees.DepartmentID = Employees.DepartmentID);

Output

Explanation:

• The subquery(SELECT AVG(Salary) FROM Employees WHERE Employees.DepartmentID = Employees.DepartmentID) computes the average salary for each employee's department.
• The outer query then updates the salary of employees whose salary is lower than the computed average salary for their department.

Important Points to Remember

• Subqueries in the WHERE Clause of an update statement allow for conditional updates based on more complex conditions.
• We can use scalar subqueries or correlated subqueries depending on the problem.
• Subqueries can be used with comparison operators like =, IN, BETWEEN, and others to compare the result of the subquery with columns in the outer query.
• A correlated subquery is especially useful when the inner query needs to reference values from the outer query, making it more dynamic.

Conclusion

Overall, Using subqueries in the WHERE clause of an UPDATE statement is a powerful technique to conditionally update records in SQL. Whether you are updating based on values from another table or from within the same table, subqueries allow for more complex and flexible data manipulation. By understanding how to utilize subqueries effectively, you can write more efficient and readable SQL queries.