MERGE操作表的更新和插入

柔情只为你懂 2022-01-28 00:49 177阅读 0赞

**情景：千万用户【公共消息表】入表一条消息即全员可见，【消息状态表】记录消息状态  
1.用户消息首次已读时入表消息状态已读  
2.用户消息删除消息时入表消息状态删除/修改已读为删除**

--1、建表
    CREATE TABLE TABLE_NAME
    (
      ID         VARCHAR2(32),
      STATUS     INTEGER
    );
    
    --2、修改或新增数据
      --逻辑：已读STATUS=0;删除STATUS>=100;未读LEFT JOIN该表NVL(STATUS,'1')
        --已读时操作
        MERGE INTO TABLE_NAME T1
        USING (SELECT '2019' AS ID,'0' AS STATUS FROM DUAL) T2
        ON (T1.ID = T2.ID)
        WHEN MATCHED THEN
             UPDATE SET T1.STATUS = T1.STATUS + T2.STATUS
        WHEN NOT MATCHED THEN
             INSERT VALUES('2019','0');
        --删除时操作
        MERGE INTO TABLE_NAME T1
        USING (SELECT '2019' AS ID,'100' AS STATUS FROM DUAL) T2
        ON (T1.ID = T2.ID)
        WHEN MATCHED THEN
             UPDATE SET T1.STATUS = T1.STATUS + T2.STATUS
        WHEN NOT MATCHED THEN
             INSERT VALUES('2019','100');
         
    --3、查询状态数数据
    SELECT * FROM TABLE_NAME;

注意：  
      UPDATE不能更新ON()中引用列，会报异常【ORA-38104：无法更新ON子句中引用的列】；  
      UPDATE会更新目标表中所有符合条件数据；

\----------------------------------下面为网上搜索信息----------------------------------

MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。

具体来说，MERGE语句会检查原数据表记录和目标表记录。如果记录在原数据表和目标表中均存在，则目标表中的记录将被原数据表中的记录所更新（执行Update操作）；如果目标表中不存在的某（些）记录，在原数据表中存在，则原数据表的这（些）记录将被插入到目标表中（执行Insert操作）。

在Oracle 10g之前，merge语句支持匹配更新和不匹配插入两种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。

**Merge语法**

MERGE \[hint\] INTO \[schema \] table \[t\_alias\]  
      USING \[schema \]\{ table | view | subquery \} \[t\_alias\]  
      ON ( condition )  
      WHEN MATCHED THEN merge\_update\_clause  
      WHEN NOT MATCHED THEN merge\_insert\_clause;

**代码及说明**

1.MERGE INTO test T1  
      2.USING (SELECT OWNER , OBJECT\_NAME , MAX(ID) ID FROM T GROUP BY OWNER, OBJECT\_NAME) T  
      3.ON (T.OWNER = T1.OWNER AND T.OBJECT\_NAME = T1.TABLE\_NAME)  
      4.WHEN MATCHED THEN UPDATE SET T1.ID = T.ID  
      5.WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT\_NAME);

第一行 命名目标表并给别名T1  
        第二行 using子句提供merge操作的数据源，命名T  
        第三行 on子句指定合并的条件  
        第四行 when matched then 子句判定条件符合则对表T1做什么改变（或删除）  
        第五行 when not matched then 子句判断条件不符合则插入的操作

**希望对你有帮助，祝你有一个好心情，加油！**

**若有错误、不全、可优化的点，欢迎纠正与补充；转载请注明出处！**