EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial Merge SQL

Merge SQL

By Payal UdhaniPayal Udhani

Merge SQL

Introduction to Merge SQL

Whenever there are two tables or multiple statements that are related and act as source and the target table such that any changes in the source table make the way for changing the contents of the target table. To do the changes on the target table whenever any changes in the source table are made, we can make the use of a merge statement in SQL which helps us to perform the insert, update and delete statements togetherly as a part of single query execution.

We can execute all the three operations involving the deletion, insertion, and updation of the target tables whenever the source statements are modified by using the merge statement at once. In this article, we will learn about the syntax of the merge statement and also learn about its implementation with the help of multiple examples.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 use the merge statement to execute any of the updates, insert and delete operations togetherly based on the records that are matched or unmatched from the target and source tables that are compared by specifying conditional like join on the column contents of both the tables and according to decide which operation should be performed on what condition for all the possible matched and unmatched all using one single statement of the merge.

In the above syntax, the target table is the name of the table on which the operations are to be performed on the occurrence of results of comparison of conditions. The source table is the name of the table whose changes will act as an event to cause changes in the target table by executing a merge statement. The search condition can be multiple conditions separated by the logical operators such as AND and OR similar to the condition specification in on clause of the joint statement. Further on the result of the conditions that are specified the flow will then be transferred either to the matched case, not matched case by target or source, and perform 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 which is as shown below –

Merge SQL-1.1

SELECT * FROM UpdatedArticles;

The execution of the above query statement gives an output which is as shown below –

Output-1.2

Now, what we have to do that the contents that are present in the updated articles table should be considered as a source table and whenever any changes are made in the source table we have to perform operations on the target table. Here, the target table is the articles table and we have to synchronize the data between articles and updated articles table.

The merge statement can be used by considering articles as the target table and updated articles as the source table. We can make the join and match based on the unique primary key columns of both tables named article id and decide that the record is matched or not by matching the contents of this column of both tables. The actions that need to be performed on comparison of the records are as mentioned below –

  • Whenever a matching id is found in the target table for source table record and the contents of article topic and rate are found to be mismatched and not equal then 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.
  • When a matching entry is not found in the source table then the delete operation is to be performed on the target table articles.
  • When a matching entry is not found in the target table then the insert operation is to be performed on the target table articles by copying the contents of that id from the source table.

Hence, according to the requirements as 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 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;
GO

The execution of the above query statement gives an output which is as shown below –

Output-1.3

We can observe that the records with id 2 and 3 existed in both the tables but varied in the rate hence rate of the source table was updated 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 hence it was deleted from the target table and for id 5 a new record was inserted in the target table updated articles as there was no such entry in it.

Conclusion

We can club multiple operations in a single merge statement when two tables are present such that changes in one table i.e. source table should make the equivalent or corresponding in another table that is 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.

  1. MySQL Row
  2. PostgreSQL Logical Replication
  3. SQL Alias
  4. PostgreSQL String Array
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more