MERGE Statement in SQL

The MERGE statement in SQL can be used to perform operations like INSERT, DELETE, and UPDATE, all within the same SQL query. In simple terms, the MERGE statement combines the separate INSERT, UPDATE, and DELETE statements into a single SQL query.

Merge in SQL merges two tables, a TARGET table, and a SOURCE table, to form a single TARGET table where entries are INSERTED, DELETED, or UPDATED based on their presence or absence on the source table.

The above picture illustrates the source and target tables with the corresponding actions: INSERT, UPDATE, and DELETE:

Suppose you have two tables, source and target, and you want to update the target table based on the source table values that match the target table. You have three cases:-

• The source table has some rows that do not exist in the target table; in that case, you need to insert those rows in the target table from the source table.
• The target table contains some rows that do not exist in the source table and that need to be deleted from the target table.
• The source table has some rows with the same keys as the rows in the target table. However, these rows have different values in the non-key columns. In this case, you need to update the rows in the target table with the values coming from the source table.

And to perform the above three cases, we have to construct three separate SQL queries. Here MERGE statements come in handy as this can be used within the same SQL query as INSERT, DELETE and UPDATE.

Why is MERGE STATEMENT Used in SQL?

MERGE in SQL helps in performing the repetitive tasks by a single query, which means that when we want to INSERT, DELETE and UPDATE data from a table. The MERGE statement comes in handy because It does three operations through a single MERGE statement.

Sometimes you need to perform INSERT, UPDATE, and DELETE operations on a target table by comparing the records from the source table. For example, a product dimension table has details about the products, and you need to sync this table with the newest product details from the source table. You need to write individual DML commands (INSERT, UPDATE and DELETE statements) to refresh the target table with an updated product list in your SQL database. Though it appears straightforward at first glance, it can evolve many works where the MERGE in SQL performs all three operations combined.

What are the Applications of the SQL MERGE Statement in SQL?

In a typical SQL data warehouse solution, it is often necessary to maintain a history of the data in the warehouse concerning the source data being fed to the ETL tool. There is a daily use case when trying to keep up with Slowly Changing Dimensions (SCD) in a data warehouse.

In such cases, you need to insert new records into the data warehouse, delete or flag those records from the warehouse that are no longer in the source, and update the values​of those in the warehouse that have been updated in the source. To perform all these operations simultaneously, you have to use the MERGE statement in the SQL.

Syntax of MERGE STATEMENT

The SQL Server provides the MERGE statement, allowing you to simultaneously perform three actions INSERT, DELETE, and UPDATE. The below code shows the syntax of the MERGE statement:

In the above syntax, we can use the merge statement to execute an update and insert and delete operations based on the records matched or unmatched from the target and source tables. Let's break down the syntax to understand it in detail.

In the first line, specify the target table and the source table in the MERGE clause.

The second line specifies the condition where the merge_condition decides how the rows from the source table are matched to the rows from the target table. Typically, you use the key columns, either a primary or unique key for matching. It is comparable to the join condition in the join clause.

And the merge_condition creates the three possible results MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE.

MERGE statement has three conditions; we will discuss each in detail below.

• MATCHED: This condition applies when the rows in both tables are matched with each other and updates the rows in the target table using the source table.
• NOT MATCHED: This condition represents the rows in the source table which are not present in the target table. NOT MATCHED is also known as the NOT MATCHED BY TARGET.
• NOT MATCHED BY SOURCE: This condition represents the rows in the target table that do not match with the source table. This condition deletes the rows in the target table to match the target table and source table.

Now that you know the MERGE statement's basic syntax and understand how it works let's take examples to understand it with real-life scenarios.

Example of MERGE Statement in SQL

The MERGE in SQL performs all three operations simultaneously to modify the data in the target table based on the source table. You can insert, update or delete data from the target table in one statement. The MERGE statement supports several clauses that enable various data modifications.

To work with merge statements, we need to have at least two tables known as the target table and source table.

For the example, we will use two tables, BookCollection and BookOrder.

BookCollection: Below is the BookCollection table, known as the target table. The BookCollection table describes the books that are available at a mythical book retailer. When the Quantity value for a book is 0, it means the book has sold out.

BookOrder: In the second, we have the BookOrder table, known as the source table. The BookOrder table displays books for which an order has been placed or delivered. If the Quantity value for a book documented in the table is 0, then the book had been requested but not possessed with the delivery. When added together, the quantity values of both tables represent the company’s current book inventory.

Now we have both target and source tables and will implement MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE conditions.

Implementing the WHEN MATCHED

The MATCHED condition applies when the rows in both tables are matched and update the rows in the target table using the source table.

For example, suppose the Book TitleID value in the BookCollection table matches the Book TitleID value in the BookOrder table. In that case, the rows are considered to match, nevertheless of the book title or the other matching values of the rows. When rows match, the WHEN MATCHED SQL clause is used to alter data in the target table. Let's look at an example to understand how this works.

In the below MERGE statement, we have joined the BookCollection table (target) to the BookOrder table (source) and then used the WHEN MATCHED SQL clause to update the Quantity column in the target table using the below code statement.

The first line of code contains the MERGE keyword, followed by the target table name, and I have assigned the alias (bc) to the target table for ease in the later code statement. And the Second line in the code is the USING clause, which is followed by the source table name and the given alias (bo) name to make ease for later in the code statement, then used an ON clause to join the two tables, based on the book TitleID value in each table (bc.TitleID = bo.TitleID).

After we followed the MATCHED condition's syntax, we specify the set subclause. The expression specifies that the new updated target table quantity value should equal the sum of the Quantity values from both the target and source tables. After that, the new quantity value in the target table will reflect the accurate number of available books for sale and the books that arrived with the recent order.

That’s all there is to creating an introductory MATCHED statement. After performing the matched condition, the updated Book Collection looks like the below:

After performing the MATCHED clause, several book quantity values are updated, and you can cross-check it with the old target table.

Implementing the WHEN NOT MATCHED

The NOT MATCHED condition represents the rows in the source table which are not present in the target table. This clause is used to insert new rows into the target table. NOT MATCHED is also known as the NOT MATCHED BY TARGET.

For example, the BookOrder table contains a row for "The Gone ". However, the BookCollection table does not contain this book. Now below, we're using the WHEN NOT MATCHED clause to add "The Gone" to the target table:

We're continuing the above code by specifying the WHEN NOT MATCHED BY TARGET keywords, followed by the THEN and the INSERT clause. The INSERT clause has the INSERT and VALUES subclause. Next, you define the source values in the VALUES subclause and target columns in the INSERT subclause. Witness that for the VALUES subclause. Next, you must qualify the column names with the alias table names.

After performing the WHEN NOT MATCHED BY TARGET clause, the updated target table contains the below values.

Two new rows are added to the bookColllection table, the first is "The Gone", and the second is the "Age of Innocence". Both books exist in the source table but not in the target table. After performing the NOT MATCHED clause, both are added to the updated target table.

Learn More

• Advanced concepts of SQL