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.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.