SQL MERGE Statement (original) (raw)

Last Updated : 9 May, 2024

**SQL MERGE Statement combines **INSERT, **DELETE, and **UPDATE statements into one single query.

MERGE Statement in SQL

MERGE statement in SQL is used to perform insert, update, and delete operations on a **target table based on the results of JOIN with a **source table. This allows users to synchronize two tables by performing operations on one table based on results from the second table.

The MERGE statement compares data between a source table and a target table based on specified key fields. It performs appropriate actions like inserting new records, updating existing ones, and deleting or flagging records no longer present in the source.

This statement provides a flexible way to manage data changes and is commonly used in scenarios like maintaining **Slowly Changing Dimensions (**SCD) in **data warehouses.

Syntax

MERGE INTO target_table
USING source_table
ON merge_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...])
VALUES (value1 [, value2 ...]);

SQL MERGE Statement Example

Suppose there are two tables:

two tables

The task is to update the details of the products in the PRODUCT_LIST as per the UPDATED_LIST.

**Solution

Now in order to explain this example better, let's split the example into steps.

**Step 1: Recognise the TARGET and the SOURCE table

So in this example, since it is asked to update the products in the PRODUCT_LIST as per the UPDATED_LIST, hence the PRODUCT_LIST will act as the TARGET and UPDATED_LIST will act as the SOURCE table.

target and source table

**Step 2: Recognise the operations to be performed.

Now as it can be seen that there are three mismatches between the TARGET and the SOURCE table, which are:

1. The cost of COFFEE in TARGET is 15.00 while in SOURCE it is 25.00

  PRODUCT_LIST

102 COFFEE 15.00

  UPDATED_LIST

102 COFFEE 25.00

2. There is no BISCUIT product in SOURCE but it is in TARGET

  PRODUCT_LIST

103 BISCUIT 20.00

3. There is no CHIPS product in TARGET but it is in SOURCE

  UPDATED_LIST

104 CHIPS 22.00

Therefore, three operations need to be done in the TARGET according to the above discrepancies. They are:

1. UPDATE operation

102 COFFEE 25.00

2. DELETE operation

103 BISCUIT 20.00

3. INSERT operation

104 CHIPS 22.00

**Step 3: Write the SQL Query

The **SQL query to perform the above-mentioned operations with the help of the **MERGE statement is:

SQL `

/* Selecting the Target and the Source */ MERGE PRODUCT_LIST AS TARGET USING UPDATE_LIST AS SOURCE 

/* 1. Performing the UPDATE operation */

/* If the P_ID is same, 
   check for change in P_NAME or P_PRICE */
ON (TARGET.P_ID = SOURCE.P_ID)
WHEN MATCHED 
     AND TARGET.P_NAME <> SOURCE.P_NAME 
     OR TARGET.P_PRICE <> SOURCE.P_PRICE

/* Update the records in TARGET */
THEN UPDATE 
     SET TARGET.P_NAME = SOURCE.P_NAME,
     TARGET.P_PRICE = SOURCE.P_PRICE
 
/* 2. Performing the INSERT operation */

/* When no records are matched with TARGET table 
   Then insert the records in the target table */
WHEN NOT MATCHED BY TARGET 
THEN INSERT (P_ID, P_NAME, P_PRICE)          
     VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)

/* 3. Performing the DELETE operation */

/* When no records are matched with SOURCE table 
   Then delete the records from the target table */
WHEN NOT MATCHED BY SOURCE 
THEN DELETE

/* END OF MERGE */

`

**Output:

PRODUCT_LIST
P_ID P_NAME P_PRICE
101 TEA 10.00
102 COFFEE 25.00
104 CHIPS 22.00

So, in this way, we can perform all these three main statements in SQL together with the help of the MERGE statement.

**Note: Any name other than target and source can be used in the MERGE syntax. They are used only to give you a better explanation.

Important Points About SQL MERGE Statement