How to Update Table Rows in SQL Server using Subquery ?
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 theHR
department. - Subquery: The subquery retrieves the
Budget
from theDepartments
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.