MERGE INTO

功能描述

MERGE INTO语句将目标表和源表中数据根据关联条件进行匹配，若关联条件匹配时对目标表进行UPDATE，不匹配时对目标表执行INSERT。MERGE INTO合并执行UPDATE和INSERT，避免多次执行以提高执行效率。

注意事项

执行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。
不支持PREPARE。
不支持重分布过程中执行MERGE INTO。
不支持对包含触发器的目标表执行MERGE INTO。
对roundrobin表执行MERGE INTO时，建议关闭GUC参数allow_concurrent_tuple_update，否则会不支持部分MERGE INTO语句。

语法格式

     
          MERGE [/*+ plan_hint */] INTO table_name [ partition_clause ] [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ partition_clause ] [ [ AS ] alias ]
ON ( condition )
[
  WHEN MATCHED THEN
  UPDATE SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
  [ WHERE condition ]
]
[
  WHEN NOT MATCHED THEN
  INSERT { DEFAULT VALUES |
  [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
];

where partition_clause can be:
PARTITION ( partition_name ) | PARTITION FOR ( partition_key_value [, ...] )

参数说明

表1 MERGE INTO参数说明
参数	描述	取值范围
plan_hint子句	以/+ /的形式在关键字后，用于对指定语句块生成的计划进行hint调优，详细用法请参见使用Plan Hint进行调优。	-
INTO子句	指定正在更新或插入的目标表。 table_name 目标表的表名。 partition_clause 用来指定目标表分区的子句。该参数9.1.0及以上集群版本支持。只有当目标表是分区表时可以指定分区。指定的分区名需要是目标表已存在的分区或者指定的分区键值所处的分区已存在。 alias 目标表的别名。	有效的表名。
USING子句	指定源表，源表可以是表、视图或子查询。 partition_clause 用来指定源表分区的子句。该参数9.1.0及以上集群版本支持。只有当源表是分区表时可以指定分区，视图或者子查询不支持指定分区。指定的分区名需要是源表已存在的分区或者指定的分区键值所处的分区已存在。 alias 目标表的别名。	有效的源表名。
ON子句	关联条件，用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。 ON关联条件可以是ctid，xc_node_id，tableoid这三个系统列。	-
WHEN MATCHED子句	当源表和目标表中数据根据关联条件匹配时，选择WHEN MATCHED子句进行UPDATE操作。不支持更新分布列。不支持更新系统表、系统列。当指定目标表的分区时，若UPDATE目标表的分区键导致分区变化：目标表是列存表时，会报错“The inserted partition key is not mapped to the specified 'partition_name' partition”。目标表是行存表时：若目标表的表属性为ENABLE ROW MOVEMENT，则不会报错，更新的数据会属于新分区。若目标表的表属性为DISABLE ROW MOVEMENT，会报错“fail to update partitioned table 'table_name' ”。	-
WHEN NOT MATCHED子句	当源表和目标表中数据根据关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。不支持INSERT子句中包含多个VALUES。 WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省，不支持同时指定两个WHEN MATCHED或WHEN NOT MATCHED子句。当指定目标表的分区时，若INSERT了超出该分区范围的数据，会报错“The inserted partition key is not mapped to the specified 'partition_name' partition”。	-
DEFAULT	用对应字段的缺省值填充该字段。	如果没有缺省值，则为NULL。
WHERE condition	UPDATE子句和INSERT子句的条件，只有在条件满足时才进行更新操作，可缺省。不支持WHERE条件中引用系统列。	-

示例

创建目标表products和源表newproducts，并插入数据。

     
      
        
        
          CREATE TABLE products
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products VALUES (1600, 'play gym', 'toys');
INSERT INTO products VALUES (1601, 'lamaze', 'toys');
INSERT INTO products VALUES (1666, 'harry potter', 'dvd');

CREATE TABLE newproducts
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');

         

       

     
    

执行MERGE INTO操作，仅当product_id字段匹配且目标表product_name字段非 'play gym' 时UPDATE，product_id字段不匹配且源表category字段为books时INSERT。

     
          MERGE INTO products p   
USING newproducts np   
ON (p.product_id = np.product_id)   
WHEN MATCHED THEN  
  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'  
WHEN NOT MATCHED THEN  
  INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

查询更新后的结果显示：目标表products的对应product_id字段1502、1666行相关数据已更新，1700行为新插入的数据。

     
          SELECT * FROM products ORDER BY product_id;
 product_id |  product_name  | category  
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1502 | olympus camera | electrncs
       1600 | play gym       | toys
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)