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:
- PRODUCT_LIST which is the table that contains the current details about the products available with fields P_ID, P_NAME, and P_PRICE corresponding to the ID, name, and price of each product.
- UPDATED_LIST which is the table that contains the new details about the products available with fields P_ID, P_NAME, and P_PRICE corresponding to the ID, name, and price of each product.

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.

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
- The SQL MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement, allowing for efficient data synchronization between source and target tables.
- It provides flexibility in customizing complex SQL scripts by handling multiple data manipulation operations in a single transaction.
- The SQL MERGE statement is commonly used in scenarios like maintaining Slowly Changing Dimensions (SCD) in data warehouses.
- Proper indexing, optimized join conditions, and filtering the source table for necessary records can optimize the performance of the MERGE statement.
Similar Reads
SQL | DESCRIBE Statement
Prerequisite: SQL Create Clause As the name suggests, DESCRIBE is used to describe something. Since in a database, we have tables, that's why do we use DESCRIBE or DESC(both are the same) commands to describe the structure of a table. Syntax: DESCRIBE one; OR DESC one; Note: We can use either DESCRI
2 min read
SQL DELETE Statement
The SQL DELETE statement is one of the most commonly used commands in SQL (Structured Query Language). It allows you to remove one or more rows from the table depending on the situation. Unlike the DROP statement, which removes the entire table, the DELETE statement removes data (rows) from the tabl
4 min read
SQL CASE Statement
The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing
4 min read
SQL SELECT IN Statement
The IN operator in SQL is used to compare a column's value against a set of values. It returns TRUE if the column's value matches any of the values in the specified list, and FALSE if there is no match.In this article, we will learn how IN operator works and provide practical examples to help you be
3 min read
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or ma
4 min read
SQL SELECT INTO Statement
The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new
5 min read
SQL INSERT INTO Statement
The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Letâs break down how this command works,
6 min read
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani
4 min read
DELETE Statement in MS SQL Server
The DELETE statement in MS SQL Server deletes specified records from the table. SyntaxMS SQL Server DELETE statement syntax is: DELETE FROM table_name WHERE condition; Note: Always use the DELETE statement with WHERE clause. The WHERE clause specifies which record(s) need to be deleted. If you exclu
1 min read
Insert Statement in MS SQL Server
The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table.In this guide, weâll explore various ways to use the Insert statement in MS SQL Server with the help
4 min read