MERGE INTO

MERGE INTO

功能描述

通过MERGE INTO语句，将目标表和源表中数据针对关联条件进行匹配，若关联条件匹配时对目标表进行UPDATE，无法匹配时对目标表执行INSERT。此语法可以很方便地用来合并执行UPDATE和INSERT，避免多次执行。

注意事项

进行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。

语法格式

1. MERGE [/*+ plan_hint */] INTO table_name [ partition_clause ] [ [ AS ] alias ]
2. USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
3. ON ( condition )
4. [
5. WHEN MATCHED THEN
6. UPDATE SET { column_name = { expression | subquery | DEFAULT } |
7. ( column_name [, ...] ) = ( { expression | subquery | DEFAULT } [, ...] ) } [, ...]
8. [ WHERE condition ]
9. ]
10. [
11. WHEN NOT MATCHED THEN
12. INSERT { DEFAULT VALUES |
13. [ ( column_name [, ...] ) ] VALUES ( { expression | subquery | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
14. ];
15. NOTICE: 'subquery' in the UPDATE and INSERT clauses are only avaliable in CENTRALIZED mode!

参数说明

plan_hint子句

以/*+ */的形式在MERGE关键字后，用于对MERGE对应的语句块生成的计划进行hint调优，详细用法请参见章节使用Plan Hint进行调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。 - INTO子句

指定正在更新或插入的目标表。 - talbe_name

目标表的表名。 - partition_clause

指定分区MERGE操作：

1. PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } | 2. SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }

关键字详见SELECT一节介绍。

如果value子句的值和指定分区不一致，会抛出异常。 - alias

目标表的别名。

取值范围：字符串，符合标识符命名规范。 - USING子句

指定源表，源表可以为表、视图或子查询。 - ON子句

关联条件，用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。 - WHEN MATCHED子句

当源表和目标表中数据针对关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE操作。

不支持更新系统表、系统列。 - WHEN NOT MATCHED子句

当源表和目标表中数据针对关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。

不支持INSERT子句中包含多个VALUES。

WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省，不支持同时指定两个WHEN MATCHED或WHEN NOT MATCHED子句。 - DEFAULT

用对应字段的缺省值填充该字段。

如果没有缺省值，则为NULL。 - WHERE condition

UPDATE子句和INSERT子句的条件，只有在条件满足时才进行更新操作，可缺省。不支持WHERE条件中引用系统列。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

示例

1. -- 创建目标表products和源表newproducts，并插入数据
2. openGauss=# CREATE TABLE products
3. (
4. product_id INTEGER,
5. product_name VARCHAR2(60),
6. category VARCHAR2(60)
7. );

9. openGauss=# INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
10. openGauss=# INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
11. openGauss=# INSERT INTO products VALUES (1600, 'play gym', 'toys');
12. openGauss=# INSERT INTO products VALUES (1601, 'lamaze', 'toys');
13. openGauss=# INSERT INTO products VALUES (1666, 'harry potter', 'dvd');

15. openGauss=# CREATE TABLE newproducts
16. (
17. product_id INTEGER,
18. product_name VARCHAR2(60),
19. category VARCHAR2(60)
20. );

22. openGauss=# INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
23. openGauss=# INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
24. openGauss=# INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
25. openGauss=# INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');

27. -- 进行MERGE INTO操作
28. openGauss=# MERGE INTO products p
29. USING newproducts np
30. ON (p.product_id = np.product_id)
31. WHEN MATCHED THEN
32. UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'
33. WHEN NOT MATCHED THEN
34. INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';
35. MERGE 4

37. -- 查询更新后的结果
38. openGauss=# SELECT * FROM products ORDER BY product_id;
39. product_id |  product_name  | category
40. ------------+----------------+-----------
41. 1501 | vivitar 35mm   | electrncs
42. 1502 | olympus camera | electrncs
43. 1600 | play gym       | toys
44. 1601 | lamaze         | toys
45. 1666 | harry potter   | toys
46. 1700 | wait interface | books
47. (6 rows)

49. -- 删除表
50. openGauss=# DROP TABLE products;
51. openGauss=# DROP TABLE newproducts;