SQL | MERGE Statement

Prerequisite – INSERT, UPDATE, DELETE

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.

Now we know that the MERGE in SQL requires two tables : one the target table on which we want to perform INSERT, UPDATE and DELETE operations, and the other one is source table which contains the new modified and correct data for target table and is actually compared with the actual target table in order to modify it.

In other words, the MERGE statement in SQL basically merges data from a source result set to a target table based on a condition that is specified. The syntax of MERGE statement can be complex to understand at first but its very easy once you know what it means.So,not to get confused first let’s discuss some basics. Suppose you have two tables: source and target, now think if you want to make changes in the required target table with the help of provided source table which consists of latest details.

• When will you need to insert the data in the target table?
  Obviously when there is data in source table and not in target table i.e when data not matched with target table.
• When will you need to update the data?
  When the data in source table is matched with target table but any entry other than the primary key is not matched.
• When will you need to delete the data?
  When there is data in target table and not in source table i.e when data not matched with source table.

Now, we know when to use INSERT, UPDATE and DELETE statements in case we want to use MERGE statement so there should be no problem for you understanding the syntax given below :

//.....syntax of MERGE statement....//

//you can use any other name in place of target
MERGE target_table_name AS TARGET  

//you can use any other name in place of source 
USING source_table_name AS SOURCE   
ON condition (for matching source and target table)
WHEN MATCHED (another condition for updation)

 //now use update statement syntax accordingly
THEN UPDATE                       
WHEN NOT MATCHED BY TARGET 

//now use insert statement syntax accordingly
THEN INSERT                        
WHEN NOT MATCHED BY SOURCE 
THEN DELETE;

That’s all about the MERGE statement and its syntax.

References –
MERGE – docs.microsoft
MERGE – docs.oracle

This article is contributed by Dimpy Varshni