Correlated Update in MS SQL Server

Updating data of one table by data of another table is very much important in Data Engineering. Which table need to be updated is called target table or destination table and from which table data will be updated is called source table. The source table may have some same rows as that are in the destination table. However, these rows have different values which need to be updated into destination table from the source table.

We have already seen an example by using MERGE for this case. In this tutorial, we will learn how to solve this problem using Correlated UPDATE. Here is the syntax of the Correlated UPDATE statement:

UPDATE alias_of_destination_table
     SET series_of_update_statement
FROM
     destination_table AS alias_of_destination_table
INNER JOIN
     source_table AS alias_of_source_table
ON
     join_conditions;

We have to specify the destination table after FROM  clause and the source table after INNER JOIN clause. The join_conditions determines how the rows from the source table are matched to the rows from the destination table. It is similar to the join condition in the general join clause. Generally we use the key columns either primary key or unique key for matching.

Let’s think we have two tables emp_offline and emp_online that keep records of employee information.

CREATE TABLE emp_offline 
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO emp_offline (id,name,email) VALUES
(1,'Fahim','fahim@gmail.com'), 
(2,'Alim','alim@gmail.com'),
(3,'Karim','karim@gmail.com'), 
(4,'Rahim','rahim@gmail.com'), 
(100,'Hakim','hakim@gmail.com');

CREATE TABLE emp_online (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO emp_online (id,name,email)
VALUES(1,'Fahim','fahim@gmail.com'),
(2,'Alim','alim@gmail.com'),
(3,'Karim','karim@yahoo.com'),
(4,'Rahim','rahim@hotmail.com');

To update data into the destination table: emp_offline with the values from the source table: emp_online, we use the following statement:

UPDATE d
     SET d.name = s.name,d.email = s.email
FROM
     emp_offline AS d
INNER JOIN
     emp_online AS s
ON
     d.id = s.id;

After executing the above statement, final emp_offline (destination) table will be updated as follows:

Description of the UPDATE operation:

• id columns will be used as identifier column for both tables as the join conditions.
• The rows with id 1, 2, 3, 4 from the emp_online table matches with the rows from the emp_offline table, therefore, the UPDATE statement updates the values in name & email columns in the emp_offline table.

After update operation, the data in the emp_offline table is fully synchronized with the data in the emp_online table. I would say it one of the important and most useful statement in SQL. You can bookmark this page for your reference.