How to Update Table Rows in SQL Server using Subquery ?

Last Updated : 11 Oct, 2024

Updating table rows in SQL Server using a subquery is a common operation that allows us to modify records based on values derived from another table or query.

In this article, we will explain how to update table rows in SQL Server using subquery with the help of examples.

Updating Table Rows Using a Subquery in SQL Server

A subquery allows us to perform updates based on related data from another table, making our operations more dynamic. We can use the UPDATE statement along with a subquery to update rows in a table based on the result of another query.

The subquery typically returns a value that is used to set the value of a column in the target table. Let’s break down the concept using a simple example.

Syntax:

UPDATE target_table
SET target_column = (
    SELECT subquery_column
    FROM subquery_table
    WHERE subquery_condition
)
WHERE target_condition;

Example to Update Table Rows in SQL Server using Subquery

Let's consider two tables: Employees and Departments. We want to update the salary of employees based on their department's budget.

Employees Table

Query:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID, Salary) VALUES
(1, 'Alice', 101, 50000),
(2, 'Bob', 102, 60000),
(3, 'Charlie', 101, 55000
);

Output:

EmployeeID	EmployeeName	DepartmentID	Salary
1	Alice	101	50000
2	Bob	102	60000
3	Charlie	101	55000

Departments Table:

Query:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Budget DECIMAL(10, 2)
);

INSERT INTO Departments (DepartmentID, DepartmentName, Budget) VALUES
(101, 'HR', 80000),
(102, 'IT', 50000);

Output:

DepartmentID	DepartmentName	Budget
101	HR	80000
102	IT	50000

Example Scenario 1: Updating Employee Salaries

We want to increase the salary of employees in the HR department (DepartmentID 101) by 10% if the department's budget exceeds 75,000.

Query:

UPDATE Employees
SET Salary = Salary * 1.10  -- Increase salary by 10%
WHERE DepartmentID = 101 AND
      (SELECT Budget 
       FROM Departments 
       WHERE DepartmentID = Employees.DepartmentID) > 75000;

Output:

EmployeeID	EmployeeName	DepartmentID	Salary
1	Alice	101	55000
2	Bob	102	60000
3	Charlie	101	60500

Explanation:

UPDATE Employees: This part specifies that we are updating the Employees table.
SET Salary = Salary * 1.10: This sets the new salary to be 10% more than the current salary.
WHERE DepartmentID = 101: This condition filters the rows in the Employees table to only those in the HR department.
Subquery: The subquery retrieves the Budget from the Departments table for the department of the employee being updated. The condition checks if the budget exceeds 75000.

Conclusion

Using subqueries to update rows in SQL Server allows for flexible and powerful data manipulation. We can conditionally update records based on related data in other tables, making SQL operations more dynamic.

With proper understanding and application of subqueries, we can improve the effectiveness of SQL queries and improve data management tasks in your applications.