Introduction The MERGE statement in SQL is a powerful feature that combines INSERT, UPDATE, and DELETE operations into a single statement. It allows you to synchronize data between two tables based on a specified condition, making it an efficient and concise way to perform data manipulation in SQL.
Syntax The basic syntax of the MERGE statement is as follows:
sqlMERGE INTO target_table AS t
USING source_table AS s
ON join_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...)
VALUES (value1, value2, ...)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
target_table
: The table where you want to apply the changes.source_table
: The table from which you want to retrieve the data for the changes.join_condition
: The condition used to match rows between the target and source tables.WHEN MATCHED THEN
: Specifies the action to perform when a match is found.WHEN NOT MATCHED THEN
: Specifies the action to perform when no match is found in the target table.WHEN NOT MATCHED BY SOURCE THEN
: Specifies the action to perform when no match is found in the source table.
Explanation and Example
Let's consider an example scenario where we have two tables: Customers
and Customers_New
. We want to update the Customers
table with the latest information from the Customers_New
table. The common column between both tables is CustomerID
.
Customers Table:
CustomerID | FirstName | LastName |
---|---|---|
1 | John | Doe |
2 | Jane | Smith |
3 | Alex | Johnson |
Customers_New Table:
CustomerID | FirstName | LastName |
---|---|---|
1 | John | Doe |
2 | Jane | Williams |
4 | Sarah | Adams |
To update the Customers
table with the latest information, we can use the following MERGE statement:
sqlMERGE INTO Customers AS c
USING Customers_New AS cn
ON c.CustomerID = cn.CustomerID
WHEN MATCHED THEN
UPDATE SET c.FirstName = cn.FirstName, c.LastName = cn.LastName
WHEN NOT MATCHED THEN
INSERT (CustomerID, FirstName, LastName)
VALUES (cn.CustomerID, cn.FirstName, cn.LastName)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
In this example:
- The
target_table
isCustomers
. - The
source_table
isCustomers_New
. - The
join_condition
isc.CustomerID = cn.CustomerID
. - When a match is found (
WHEN MATCHED
), it updates theFirstName
andLastName
columns in theCustomers
table. - When no match is found in the
Customers
table (WHEN NOT MATCHED
), it inserts a new row into theCustomers
table. - When no match is found in the
Customers_New
table (WHEN NOT MATCHED BY SOURCE
), it deletes the corresponding row from theCustomers
table.
After executing the MERGE statement, the Customers
table will be updated as follows:
CustomerID | FirstName | LastName |
---|---|---|
1 | John | Doe |
2 | Jane | Williams |
3 | Alex | Johnson |
4 | Sarah | Adams |
The MERGE statement provides a flexible and efficient way to handle data synchronization and manipulation tasks in SQL, reducing the need for multiple separate statements.
0 Comments