What is a MERGE statement in SQL?
The MERGE statement merges the functionality of the UPDATE, INSERT, and DELETE operations. It eliminates the need for multiple separate statements by combining the following tasks into a single operation:
Updating existing records
Inserting new records
Removing obsolete records
Structure
The MERGE statement allows us to modify the target table by comparing it with the source table based on the specified condition; this involves aligning the rows in both tables to track the matched and unmatched instances.
Let’s break this down further.
Source table
The source table is either a subquery or another table that provides the dataset that needs to be incorporated into the target table.
Target table
The target table contains the dataset where the MERGE operation performs the modification; it’s the destination where the merged data is saved. This table is either updated, inserted to, or deleted from.
Condition
The condition is essential to the MERGE statement; it is a matching condition that determines how the rows from the source table and the target table will align.
Matched/Unmatched instances
After the alignment, it is determined which rows need to be updated, inserted, or deleted. When the source table and the target table share a common attribute, as per the condition, it is called a match. The specified attributes in the matched rows may be updated, and the unmatched rows may either be removed or inserted from the source table into the target table.
Syntax
The following code shows the format of the MERGE statement:
MERGE INTO target_table_name AS targetUSING source_table_name AS sourceON target.join_conditionWHEN MATCHED THEN-- UpdateUPDATE SET target.column1 = source.column1, target.column2 = source.column2WHEN NOT MATCHED BY TARGET THEN-- InsertINSERT (column1, column2, ...)VALUES (source.column1, source.column2, ...)WHEN NOT MATCHED BY SOURCE THEN-- DeleteDELETE;
The clauses WHEN MATCHED THEN and WHEN NOT MATCHED BY TARGET THEN are compulsory for every MERGE statement, while the WHEN NOT MATCHED BY SOURCE THEN clause is optional.
Example
Let’s look at an example of how to use the MERGE statement.
Note: We will be used the two tables given below in this example.
Customers table (Target): This is the main table that has the data of the existing customers.
CustomerID | Name | |
1 | John Doe | john@example.com |
2 | Jane Smith | jane@example.com |
3 | Alex Brown | alex@example.com |
4 | Emily Watson | emily@example.com |
5 | Micheal Jackson | micheal@example.com |
NewCustomers table (Source): This is the table that stores the data of the new customers.
CustomerID | Name | |
6 | Susan Walker | susan@example.com |
7 | Lisa Jones | lisa@example.com |
1 | John Doe | john@newexample.com |
4 | Emily Watson | emily@example.com |
In the following query, the target and source tables are given aliases: Target and Source, respectively. The matching condition is based on the CustomerID. In the case of a matched instance, the Name and Email attributes in Target are updated and set according to the data in the Source. Otherwise, the rows (with all three attributes) from the Source are appended to the Target.
MERGE INTO Customers AS TargetUSING NewCustomers AS SourceON Target.CustomerID = Source.CustomerIDWHEN MATCHED THENUPDATE SETTarget.Name = Source.Name,Target.Email = Source.EmailWHEN NOT MATCHED BY TARGET THENINSERT (CustomerID, Name, Email)VALUES (Source.CustomerID, Source.Name, Source.Email);
The output of this MERGE statement will be:
CustomerID | Name | |
1 | John Doe | john@newexample.com |
2 | Jane Smith | jane@example.com |
3 | Alex Brown | alex@example.com |
4 | Emily Watson | emily@example.com |
5 | Micheal Jackson | micheal@example.com |
6 | Susan Walker | susan@example.com |
7 | Lisa Jones | lisa@example.com |
Try it yourself
Experiment around with the MERGE statement by applying different conditions and observe how the output varies. You can write your code in the code widget below.
Sample data is already provided, so you don’t need to worry about table creation or insertion.
-- create and populate CustomersCREATE TABLE Customers (CustomerID INT PRIMARY KEY,Name VARCHAR(255) NOT NULL,Email VARCHAR(255) NOT NULL);INSERT INTO Customers (CustomerID, Name, Email)VALUES(1, 'John Doe', 'john@example.com'),(2, 'Jane Smith', 'jane@example.com'),(3, 'Alex Brown', 'alex@example.com'),(4, 'EmiLY Watson', 'emily@example.com'),(5, 'Micheal Jackson', 'micheal@example.com');-- create and populate NewCustomersCREATE TABLE NewCustomers (CustomerID INT PRIMARY KEY,Name VARCHAR(255) NOT NULL,Email VARCHAR(255) NOT NULL);INSERT INTO NewCustomers (CustomerID, Name, Email)VALUES(6, 'Susan Walker', 'susan@example.com'),(7, 'Lisa Jones', 'lisa@example.com'),(1, 'John Doe', 'john@newexample.com'),(4, 'Emily Watson', 'emily@example.com');-- Experiment with different MERGE statement conditions. Happy learning!
Conclusion
The MERGE statement combines UPDATE, INSERT, and DELETE functions. It aligns rows between source and target tables based on the specified conditions by allowing updates, inserts, and deletions. Essential components include source and target tables, a matching condition, and handling matched/unmatched instances. The MERGE statement’s syntax involves ON, WHEN MATCHED, and WHEN NOT MATCHED clauses.
Free Resources