Subqueries in the WHERE Clause of UPDATE in SQL

In SQL, an UPDATE statement modifies existing records in a table. When updating records, we can use a subquery within the WHERE clause to conditionally update only specific rows based on results from another query.

In this article, we will learn how to write Subqueries in the WHERE Clause to Update the data in the SQL table in detail by understanding various examples.

What is a Subquery 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.

Syntax:

Basic syntax for using a subquery within the WHERE clause of an UPDATE statement:

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

Explanation:

• 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 Subqueries in the WHERE Clause of UPDATE in SQL

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:

Employees Table:

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 of Updated Employees Table:

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.

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.

Benefits of Using Subqueries in the WHERE Clause of UPDATE

• Conditional Updates: Subqueries allow you to update records based on complex conditions that require data from other tables or computations within the same table.
  
• Increased Flexibility: Using a subquery provides flexibility by allowing you to filter rows based on aggregated results.
  
• Simplification: It simplifies the query when you need to filter records based on another set of data by eliminating the need for temporary tables or multiple queries.

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.