MERGE Statement in SQL Explained

Prerequisite – MERGE Statement
MERGE statement in SQL as discussed before is the combination of three INSERT, DELETE and UPDATE statements and with the help of MERGE statement we can perform all these three operations in our main target table when source table provided.

Let’s clarify all our doubts of MERGE statement with the help of example. Consider we have a target table PRODUCTS_DETAILS in which we have to use these operations and also we have a source table UPDATED_DETAILS which contains latest product details and according to the data provided in the UPDATED_DETAILS table we want to make changes in the main PRODUCT_DETAILS table.

          PRODUCT_DETAILS
P_ID    P_NAME    P_PRICE
101     TEA       10.00 
102     COFFEE    15.00
103     BISCUIT   20.00

Now, consider the source table :



          UPDATED_DETAILS
P_ID    P_NAME    P_PRICE
101     TEA       10.00 
102     COFFEE    25.00
104     CHIPS     22.00

Now, as we can see if we want to make changes in PRODUCT_DETAILS table according to the UPDATED_DETAILS table we will have to use INSERT statement for P_ID 104, UPDATE statement for P_ID 102 and DELETE statement for P_ID 103 so for using all these three statements together we can use one single statement MERGE. Let’s find out below:
Before reading the below SQL query read about the MERGE statement and its syntax in the other post.

MERGE PRODUCT_DETAILS AS TARGET
USING UPDATED_DETAILS AS SOURCE 
ON (TARGET.P_ID = SOURCE.P_ID)              
                                                                                     

THEN MATCHED AND TARGET.P_NAME  SOURCE.P_NAME 
OR TARGET.P_PRICE  SOURCE.P_PRICE THEN 

//when records are matched (on the basis  
//of P_ID) then do the update operation 
UPDATE SET TARGET.P_Name = SOURCE.P_NAME,    

//if there are changes in P_NAME OR P_PRICE
TARGET.P_PRICE = SOURCE.P_PRICE         
 
WHEN NOT MATCHED BY TARGET THEN  

//When no records are matched with target table 
//then insert the records in the target table
INSERT (P_ID,P_NAME,P_PRICE)          
VALUES (SOURCE.P_ID,SOURCE.P_NAME,SOURCE.P_PRICE)

WHEN NOT MATCHED BY SOURCE THEN         

//when no records are matched with source table 
//the delete that record in target table
DELETE;                              

So, this is how we can use MERGE statement in SQL.Now,let’s find out how the PRODUCT_DETAILS table looks after running the above SQL query.

          PRODUCT_DETAILS
P_ID    P_NAME    P_PRICE
101     TEA       10.00 
102     COFFEE    25.00
104     CHIPS     22.00

So, in this way all we can perform all these three main statements in SQL together with the help of MERGE statement.

Note – You can use any name other than target and source in the MERGE syntax, they are used only to give you better explanation.

This article is contributed by Dimpy Varshni. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.