#1 Spot for Defeating Online Exams

how-to-update-with-join-in-sql-server?

Response: You can utilize an UPDATE statement along with JOIN in SQL Server by employing the UPDATE command in combination with INNER JOIN.

In SQL Server, the UPDATE operation with a join permits you to modify records in one table based on corresponding records from another table. In this article, let’s examine the various methods for implementing an UPDATE statement coupled with JOIN in SQL Server thoroughly, including examples for each.

Table of Contents:

Techniques for UPDATE with JOIN in SQL Server

Before we delve into the methods, let’s construct two tables that will serve as examples for this article.

Firstly, let’s create an Employee table featuring 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 ;
Mastering SQL Server: Using JOINs to Update Your Data Efficiently

This is the appearance of the Employee table once it has been created and populated with values.

Create a Department table which includes 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 ;
Mastering SQL Server: Using JOINs to Update Your Data Efficiently

The Department table appears like this once created and filled with data.

Technique 1: Utilizing UPDATE with INNER JOIN in SQL Server

The INNER JOIN in SQL retrieves the rows that contain matching data in both tables. When this UPDATE statement is executed with INNER JOIN, it guarantees that only those records in the target table that have corresponding matches in the joined table are modified.

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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation:

  • This updates only the employees with a matching DepartmentID in both tables.
  • Employees with EmployeeID 1, 2, and 3 are revised, while the EmployeeID 4 remains unchanged due to the absence of a match for DepartmentID = 104.

Technique 2: Utilizing UPDATE with LEFT JOIN in SQL Server

UPDATE with LEFT JOIN is applied when the requirement is to modify a table based on information from another table while ensuring that all rows from the left (main) table are retained, even if no corresponding row exists 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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation: Employees with corresponding DepartmentIDs have had their salaries updated accordingly.

Technique 3: Utilizing UPDATE with RIGHT JOIN in SQL Server

RIGHT JOIN is utilized when we want to retain every record from the right (second) table and update the left (first) table only when a match is present.

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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation: Within SQL Server, RIGHT JOIN guarantees that all departments are included, but since we are updating the Employee records, only those Employees that exist in the first table are influenced.

Technique 4: Utilizing CTE with JOIN to UPDATE the table in SQL Server

In SQL Server, we can employ a Common Table Expression…

(CTE) utilizing JOIN to modify a table can be accomplished by:

  • Establishing the CTE that gathers the required information through a JOIN.
  • Implementing the UPDATE Statement that references 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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation: Since the DeptID (104) for EmployeeID 4 does not exist in the Department table, it remains unmodified. In SQL Server, if an attempt is made to update a column with no corresponding value in the JOIN, it will return a NULL value.

Alternatives to UPDATE with JOIN in SQL Server

While USING UPDATE with JOIN is an effective approach for modifying records across related tables in SQL Server, various alternative techniques exist for accomplishing the update. Below are some options:

Subqueries

By outlining conditions in the subquery, we can avoid the complications of multiple 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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation:

Only those employees with a matching DeptID in the Department table undergo updates. Any employees with a non-existent DeptID in the Department table stay unchanged.

Merge Statement

MERGE is a sophisticated technique 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:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Explanation: The MERGE and USING statements delineate the target and source tables for updates.

Updating Multiple Columns with JOIN in SQL Server

It is possible to modify several columns simultaneously by joining tables. To carry out multiple column updates in SQL Server via JOIN, you can utilize the following syntax.

Syntax:

UPDATE t1
SET 
    t1.Column1 = t2.Column1,
    t1.Column2 = t2.Column2,
    t1.Column3 = t1.Column3 + t2.Column3  -- Additional operations are permissible
FROM Table1 t1
JOIN Table2 t2 
ON t1.CommonColumn = t2.CommonColumn;

Query:

UPDATE e
SET 
    e.Salary = e.Salary + d.Bonus,    -- Increasing Salary with Bonus
    e.DeptID = d.DeptID                    -- Modifying DeptID
FROM Employee e
JOIN Department d 
ON e.DeptID = d.DeptID;

Output:

Mastering SQL Server: Using JOINs to Update Your Data Efficiently

Performance Evaluation of Each Technique

Method Use Case Advantages Disadvantages
UPDATE with INNER JOIN For modifying records that possess matching values in both tables. Quicker than LEFT JOIN and RIGHT JOIN. Records remain unchanged if there are no matching records.
UPDATE with LEFT JOIN To update all entries from the left table (Employee), regardless of matches in the right table (Department). Guarantees that all employees are modified even without matches in the Department. Slower than INNER JOIN due to processing every record.
UPDATE with RIGHT JOIN To modify all records from the right table (Department) that have matching values in the left table. Ensures all departments are taken into account, even if no employees exist. Slower than INNER and LEFT JOIN due to unnecessary updates.
CTE with JOIN to UPDATE When updates are needed based on multiple conditions. Generally performs better with larger datasets by eliminating redundant calculations. May be less efficient in smaller datasets due to CTE overhead.

Conclusion

Utilize UPDATE with JOIN for effective results from extensive datasets, while UPDATE with Subquery is better suited for smaller datasets, as subqueries can hinder the performance of substantial updates. When the need arises to perform both updates and inserts in one go, the MERGE statement is preferable for superior performance. Hence, this article detailed the process for updating a statement using JOIN in SQL Server.

The article How to UPDATE with JOIN in SQL Server? was first published on Intellipaat Blog.

Staff SEO Expert

Leave a Reply

Your email address will not be published. Required fields are marked *

Share This