Merge Statement in SQL

Merge Statement in SQL

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:

sql
MERGE 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:

sql
MERGE 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 is Customers.
• The source_table is Customers_New.
• The join_condition is c.CustomerID = cn.CustomerID.
• When a match is found (WHEN MATCHED), it updates the FirstName and LastName columns in the Customers table.
• When no match is found in the Customers table (WHEN NOT MATCHED), it inserts a new row into the Customers table.
• When no match is found in the Customers_New table (WHEN NOT MATCHED BY SOURCE), it deletes the corresponding row from the Customers 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.