Answer: You can use an UPDATE statement with JOIN in SQL Server using UPDATE along with INNER JOIN.
In SQL Server, UPDATE with join operation allows you to update records in one table based on the matching records from another table. In this blog, let’s explore the different approaches to using an UPDATE statement along with JOIN in SQL Server in detail with examples for each.
Table of Contents:
Methods to UPDATE with JOIN in SQL Server
Before getting into the methods let us create two tables that can be used as an example in this blog.
Firstly, create an Employee table that consists of EmployeeID, DeptID, and Salary
-- To create an Employee table
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
DeptID INT,
Salary DECIMAL(10, 2)
);
--Insert values into the table
INSERT INTO Employee (EmployeeID, DeptID, Salary) VALUES
(1, 101, 50000),
(2, 102, 52000),
(3, 103, 48000),
(4,104,56000);
-- To display the content of the table
Select * from Employee ;
This is how the Employee table looks once created and inserted with values.
Create a Department table which consists of DeptID, Deptname, and Bonus
-- To create a table
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50),
Bonus DECIMAL(10, 2)
);
-- Insert values into the table
INSERT INTO Department (DeptID, DeptName, Bonus) VALUES
(101, 'TCW', 5000),
(102, 'TRA', 7000),
(103, 'BDA', 4500);
-- To display the content of the table
Select * from Department ;
The Department table looks like this once it is created and inserted with values.
Method 1: Using UPDATE with INNER JOIN in SQL Server
The INNER JOIN in SQL retrieves the rows/records with the matching data in both tables. When this UPDATE statement is used with the INNER JOIN, it ensures that only those records in the target table that have the corresponding matches in the joined table are updated.
Syntax:
UPDATE T1
SET T1.column_to_update = T2.column_value
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.common_column = T2.common_column
WHERE <optional_condition>;
Query:
UPDATE e
SET e.Salary = e.Salary + d.Bonus
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID;
Output:
Explanation:
- It updates only the employees with a matching DepartmentID in both tables.
- Employees with EmployeeID 1, 2 and 3 are updated, since there is no match for DepartmentID = 104 it remains the same.
Method 2: Using UPDATE with LEFT JOIN in SQL Server
UPDATE with LEFT JOIN is used when you need to update a table based on data from another table while ensuring that all rows from the left (main) table are preserved, even if there is no matching row in the joined table.
Syntax:
UPDATE T1
SET T1.column_to_update = T2.column_value
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.common_column = T2.common_column
WHERE <optional_condition>;
Query:
UPDATE Employee e
LEFT JOIN Department d
ON e.DeptID = d.DeptID
SET e.Salary = e.Salary + IFNULL(d.Bonus, 0);
Output:
Explanation: Employees with matching DepartmentID have been updated with the salary.
Method 3: Using UPDATE with RIGHT JOIN in SQL Server
RIGHT JOIN is used when we need to keep every record of the right (second) table and update the left (first) table only if the match exists.
Syntax:
UPDATE e
SET e.Salary = e.Salary + ISNULL(d.Bonus, 0)
FROM Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID;
Query:
UPDATE Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID
SET e.Salary = e.Salary + IFNULL(d.Bonus, 0);
Output:
Explanation: In SQL Server, RIGHT JOIN ensures all departments are considered, but since we are updating Employees, only existing employees are affected.
Method 4: Using CTE with JOIN to UPDATE the table in SQL Server
In SQL Server, we can use a Common Table Expression (CTE) with JOIN to update a table which can be done by,
- Defining the CTE that retrieves the necessary data using a JOIN.
- Using the UPDATE Statement referencing the CTE.
Syntax:
WITH CTE AS (
SELECT
Table1.PrimaryKeyColumn,
Table1.ColumnToUpdate,
Table2.ColumnWithNewValue
FROM Table1
JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn
)
UPDATE CTE
SET ColumnToUpdate = ColumnWithNewValue;
Query:
WITH CTE AS (
SELECT e.EmployeeID, e.Salary, d.Bonus
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
)
UPDATE Employee
SET Salary = Salary + (SELECT Bonus FROM CTE WHERE CTE.EmployeeID = Employee.EmployeeID);
Select * from Employee;
Output:
Explanation: Since EmployeeID 4’s DeptID (104) does not exist in the Department table, it does not get updated. In SQL Server if you try to update a column and there is no matching value in the JOIN then it returns NULL value.
Alternatives of UPDATE with JOIN in SQL Server
Although UPDATE with JOIN is a powerful method for updating the records across the related tables in SQL Server, there are few alternative methods for achieving the update. The following are some options:
Subqueries
By specifying the conditions in the subquery, we will be able to avoid multiple complex tables.
Syntax:
UPDATE target_table
SET column_name = column_name + (
SELECT source_column
FROM source_table
WHERE target_table.join_column = source_table.join_column
)
WHERE join_column IN (SELECT join_column FROM source_table);
Query:
UPDATE Employee
SET Salary = Salary + (
SELECT Bonus
FROM Department
WHERE Employee.DeptID = Department.DeptID
)
WHERE DeptID IN (SELECT DeptID FROM Department);
Output:
Explanation:
Only employees whose DeptID exists in the Department table get updated. Employees with the DeptID not found in the Department table remain unchanged.
Merge Statement
MERGE is an advanced method for updating, deleting, or inserting rows in the target table based on the source table.
Syntax:
MERGE INTO target_table AS t
USING source_table AS s
ON t.join_column = s.join_column
WHEN MATCHED THEN
UPDATE SET t.column_name = t.column_name + s.source_column;
Query:
MERGE INTO Employee AS e
USING Department AS d
ON e.DeptID = d.DeptID
WHEN MATCHED THEN
UPDATE SET e.Salary = e.Salary + d.Bonus;
Output:
Explanation: MERGE and USING statement defines the target and source table to update.
Updating Multiple Columns with JOIN in SQL Server
You can update multiple columns at once by joining tables. To update multiple columns in SQL Server using JOIN, you can follow this syntax.
Syntax:
UPDATE t1
SET
t1.Column1 = t2.Column1,
t1.Column2 = t2.Column2,
t1.Column3 = t1.Column3 + t2.Column3 -- Can also perform operations
FROM Table1 t1
JOIN Table2 t2
ON t1.CommonColumn = t2.CommonColumn;
Query:
UPDATE e
SET
e.Salary = e.Salary + d.Bonus, -- Updating Salary with Bonus
e.DeptID = d.DeptID -- Updating DeptID
FROM Employee e
JOIN Department d
ON e.DeptID = d.DeptID;
Output:
| Method | Use case | Pros | Cons |
| UPDATE with INNER JOIN | When we want to update records that have match values in both tables. | Faster than LEFT JOIN and RIGHT JOIN | Do not update records if there aren’t any matching records |
| UPDATE with LEFT JOIN | When we need to update all records from the left table (Employee) even if there’s no match in the right table (Department). | Ensures that all employees are updated even if no match is found in the Department. | Slower than INNER JOIN because it processes every record. |
| UPDATE with RIGHT JOIN | When we need to update all records from the right table ( Department) that have matching values in the left table. | Ensures all departments are considered even if they have no employees. | Slower than INNER and LEFT JOIN because of the unnecessary updates |
| CTE with JOIN to UPDATE | When we need to update records on multiple conditions | Often performs better in larger datasets by reducing the reductant calculations | Might be slower in smaller datasets because of CTE overhead. |
Conclusion
Use UPDATE from JOIN for fetching effective results from large datasets whereas UPDATE with Subquery can be used for small datasets since subqueries can slow down large updates. When updating and inserting are required to be done in a single query then we can use the MERGE statement for better performance. Thus in this article, we learned how to UPDATE a statement with JOIN in SQL Server.