MERGE Statement in SQL Explained

Prerequisite – MERGE Statement
As MERGE statement in SQL, as discussed before in the previous post, is the combination of three INSERT, DELETE and UPDATE statements. So if there is a Source table and a Target table that are to be merged, then with the help of MERGE statement, all the three operations (INSERT, UPDATE, DELETE) can be performed at once.

A simple example will clarify the use of MERGE Statement.
Example:

Suppose there are two tables:



  1. PRODUCT_LIST which is the table that contains the current details about the products available with fields P_ID, P_NAME, and P_PRICE corresponding to the ID, name and price of each product.
  2. UPDATED_LIST which is the table that contains the new details about the products available with fields P_ID, P_NAME, and P_PRICE corresponding to the ID, name and price of each product.

The task is to update the details of the products in the PRODUCT_LIST as per the UPDATED_LIST.

Solution
Now in order to explain this example better, let’s split the example into steps.

  • Step 1: Recognise the TARGET and the SOURCE table
    So in this example, since it is asked to update the products in the PRODUCT_LIST as per the UPDATED_LIST, hence the PRODUCT_LIST will act as the TARGET and UPDATED_LIST will act as the SOURCE table.

  • Step 2: Recognise the operations to be performed.
    Now as it can be seen that there are three mismatches between the TARGET and the SOURCE table, which are:

    1. The cost of COFFEE in TARGET is 15.00 while in SOURCE it is 25.00
            PRODUCT_LIST
      102     COFFEE    15.00
      
            UPDATED_LIST
      102     COFFEE    25.00
      
    2. There is no BISCUIT product in SOURCE but it is in TARGET
            PRODUCT_LIST
      103     BISCUIT   20.00
      
    3. There is no CHIPS product in TARGET but it is in SOURCE
            UPDATED_LIST
      104     CHIPS     22.00
      

    Therefore, three operations need to be done in the TARGET according to the above discrepancies. They are:

    1. UDPATE operation
      102     COFFEE    25.00
      
    2. DELETE operation
      103     BISCUIT   20.00
      
    3. INSERT operation
      104     CHIPS     22.00
      
  • Step 3: Write the SQL Query.

    Note: Refer this post for the syntax of MERGE statement.

    The SQL query to perform the above-mentioned operations with the help of MERGE statement is:

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    /* Selecting the Target and the Source */
    MERGE PRODUCT_LIST AS TARGET
        USING UPDATE_LIST AS SOURCE 
      
        /* 1. Performing the UPDATE operation */
      
        /* If the P_ID is same, 
           check for change in P_NAME or P_PRICE */
        ON (TARGET.P_ID = SOURCE.P_ID)
        WHEN MATCHED 
             AND TARGET.P_NAME <> SOURCE.P_NAME 
             OR TARGET.P_PRICE <> SOURCE.P_PRICE
      
        /* Update the records in TARGET */
        THEN UPDATE 
             SET TARGET.P_NAME = SOURCE.P_NAME,
             TARGET.P_PRICE = SOURCE.P_PRICE
           
        /* 2. Performing the INSERT operation */
      
        /* When no records are matched with TARGET table 
           Then insert the records in the target table */
        WHEN NOT MATCHED BY TARGET 
        THEN INSERT (P_ID, P_NAME, P_PRICE)          
             VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
      
        /* 3. Performing the DELETE operation */
      
        /* When no records are matched with SOURCE table 
           Then delete the records from the target table */
        WHEN NOT MATCHED BY SOURCE 
        THEN DELETE
      
    /* END OF MERGE */

    chevron_right

    
    

  • Output:

           PRODUCT_LIST
    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: Any name other than target and source can be used in the MERGE syntax. They are used only to give you a 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

    Improved By : RishabhPrabhu



    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.