MERGE INTO

功能描述

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

注意事项

  • 进行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。
  • 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。原openGauss的MERGE INTO语法请参考章节MERGE INTO。
  • 新增支持table_hint子句。

语法格式

1. MERGE [/*+ plan_hint */] INTO table_name [ partition_clause ] [ [ AS ] alias ]
2. USING { { table_name | view_name } | subquery } [ [ AS ] alias ] [table_hint_clause]
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!
  • 其中table_hint子句table_hint_clause为：

1. WITH ( <table_hint> [, ...] )

参数说明

  • WITH ( <table_hint> [, …] )

  • 与SELECT子句相同，在未给出WITH时，table_hint仅支持给出一个hint，在给出WITH时，table_hint支持给出一个列表选项，列表通过逗号或者空格分隔，即(hint1)、WITH (hint1)、WITH (hint1, hint2, …)、WITH (hint1 hint2 …)均支持。

  • 支持的hint包括NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK、SNAPSHOT、NOEXPAND。
  • 当上述hint需要当做标识符，用于列名、变量名等，需要设置d_format_behavior_compat_options = ‘enable_table_hint_identifier’，该变量默认值d_format_behavior_compat_options = ‘’。
  • 所有的hint仅语法支持，无实际含义。
  • 针对hint，会打印相关NOTICE信息。

示例

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. -- 进行MERGE INTO操作
50. MERGE INTO products p
51. USING newproducts np with (nowait)
52. ON (p.product_id = np.product_id)
53. WHEN MATCHED THEN
54. UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'
55. WHEN NOT MATCHED THEN
56. INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';
57. NOTICE:  The nowait option is currently ignored
58. MERGE 4

60. -- 查询更新后的结果
61. openGauss=# SELECT * FROM products with (nowait) ORDER BY product_id;
62. NOTICE:  The nowait option is currently ignored
63. product_id |  product_name  | category
64. ------------+----------------+-----------
65. 1501 | vivitar 35mm   | electrncs
66. 1502 | olympus camera | electrncs
67. 1600 | play gym       | toys
68. 1601 | lamaze         | toys
69. 1666 | harry potter   | toys
70. 1700 | wait interface | books
71. (6 rows)

73. -- 进行MERGE INTO操作
74. MERGE INTO products p
75. USING newproducts np (nowait)
76. ON (p.product_id = np.product_id)
77. WHEN MATCHED THEN
78. UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'
79. WHEN NOT MATCHED THEN
80. INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';
81. NOTICE:  The nowait option is currently ignored
82. MERGE 4

84. -- 查询更新后的结果
85. openGauss=# SELECT * FROM products with (nowait) ORDER BY product_id;
86. NOTICE:  The nowait option is currently ignored
87. product_id |  product_name  | category
88. ------------+----------------+-----------
89. 1501 | vivitar 35mm   | electrncs
90. 1502 | olympus camera | electrncs
91. 1600 | play gym       | toys
92. 1601 | lamaze         | toys
93. 1666 | harry potter   | toys
94. 1700 | wait interface | books
95. (6 rows)

97. -- 删除表
98. openGauss=# DROP TABLE products;
99. openGauss=# DROP TABLE newproducts;

相关链接

MERGE INTO