• MERGE Statement in SQL Explained


    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: 

    • 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.
    • 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.

    target有101,102,103;  source是101,202,104

    101不变;102数值从15更新为20;103删除;104插入;

    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   更新102

          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. UPDATE 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:

    /* 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 */

    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.

  • 相关阅读:
    谈谈入职新公司1月的体会
    来点高逼格的,使用前端Sendmessage实现SSO
    2019做的第一个艰难决定
    Golang中设置函数默认参数的优雅实现
    linux系统shell基础知识入门二
    在AWS中自定义Credential Provider实现Client连接
    linux系统shell基础知识入门
    初学者学习golang可能遇到的坑
    【Menu】 目录索引
    rsync 服务介绍及相关实验
  • 原文地址:https://www.cnblogs.com/chucklu/p/16543901.html
Copyright © 2020-2023  润新知