Updated June 16, 2023
Introduction to Merge SQL
Whenever there are two tables or multiple related statements, act as the source and the target table such that any changes in the source table make way for changing the contents of the target table. To ensure the reflection of changes made in the source table to the target table, we can utilize a merge statement in SQL. This statement allows us to execute insert, update, and delete operations as a single query, ensuring synchronization between the two tables.
The merge statement empowers us to perform all three operations—deletion, insertion, and updating—on the target tables whenever we modify the source statements. In this article, we will learn about the syntax of the merge statement and its implementation with the help of multiple examples.
Syntax of Merge SQL
The syntax of the MERGE statement in SQL is as shown below:
MERGE <target table> [AS TARGET]
USING <source table> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED
THEN <merge the matched records> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <perform merge operations when matching record is not found in the target table> ]
[WHEN NOT MATCHED BY SOURCE
THEN <perform merge operations when matching record is not found in the source table> ];In the above statement, we can utilize the merge statement to collectively execute updates, insertions, and deletions based on matched or unmatched records between the target and source tables. By specifying conditions, such as joining the column contents of both tables, we determine which operation should be performed under each condition. Using a single statement, the merge statement allows us to perform all possible operations on matched and unmatched records.
In the above syntax, we refer to the table’s name as the target table, where the operations are performed based on the results obtained from comparing conditions. The source table is the table’s name whose changes will act as an event to cause changes in the target table by executing a merge statement. The search condition can consist of multiple conditions separated by logical operators such as AND and OR, similar to specifying conditions in a clause of a joint statement. Furthermore, based on the results of the specified conditions, the flow will transfer to either the matched case or the not matched case, determined by the target or source, and carry out the desired operations.
Examples of Merge SQL
Let us understand the implementation of the merge statement with the help of an example. Whenever using the merge statement, it is necessary to identify the source table, target table, and the operations that need to be performed on the target table whenever the changes are being made in the source statement. Consider the two existing tables named Articles and Updated Articles, whose contents and structure are as shown in the output of the following query statement:
SELECT * FROM Articles;The execution of the above query statement gives an output that is as shown below –
SELECT * FROM UpdatedArticles;The execution of the above query statement gives an output that is as shown below –
Now, what we have to do is that the contents in the updated articles table should be considered a source table. We must perform operations on the target table whenever we change the source table. Here, the target table is the articles table, and we have to synchronize the data between the articles and the updated articles table.
We can utilize the merge statement by considering articles as the target table and updated articles as the source table. The join and matching are performed based on both tables’ unique primary key columns, referred to as article id. We determine whether a record is matched by comparing the contents of this column in both tables. When comparing the records, we need to perform the following actions:
- Whenever a matching id is found in the target table for the source table record and the contents of the article topic and rate are mismatched and unequal. We will have to perform the update operation and update the contents of the target table articles according to the contents of that record in the source table.
- If the source table does not have a matching entry, we will perform the delete operation on the target table ‘articles’.
- If the target table does not have a matching entry, we should perform the insert operation on the target table ‘articles’ by copying the contents of that ID from the source table.
Hence, according to the requirements mentioned above for performing multiple operations on the target table using a single merge statement, we can write the merge statement as shown below, which will satisfy all the conditions and perform the necessary operations.
MERGE Articles AS TARGET
USING UpdatedArticles AS SOURCE
ON (TARGET.ArticleID = SOURCE.ArticleID)
WHEN MATCHED AND TARGET.ArticleTopic <> SOURCE.ArticleTopic OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ArticleTopic = SOURCE.ArticleTopic, TARGET.Rate = SOURCE.Rate
WHEN NOT MATCHED BY TARGET
THEN INSERT (ArticleID, ArticleTopic, Rate) VALUES (SOURCE.ArticleID, SOURCE.ArticleTopic, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $ACTION,
DELETED.ArticleID AS TargetArticleID,
DELETED.ArticleTopic AS TargetArticleTopic,
DELETED.Rate AS TargetRate,
INSERTED.ArticleID AS SourceArticleID,
INSERTED.ArticleTopic AS SourceArticleTopic,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GOThe execution of the above query statement gives an output that is as shown below –
We can observe that the records with id 2 and 3 existed in both the tables but varied in rate; Therefore, we actively updated the rate of the source table in the target table by executing an update query on the target table. For id with 4, there was no record in the updated articles source table; We actively deleted it from the target table and, for ID 5, inserted a new record into the target table. Additionally, we updated articles as there was no existing entry.
Conclusion
We can club multiple operations in a single merge statement when two tables are present such that changes in one table, i.e., the source table, should make the equivalent or corresponding in another table, the target table.
Recommended Articles
We hope that this EDUCBA information on “Merge SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
