Oracle兼容-语法-MERGE INTO

1. 语法

1. MERGE INTO target_tab_name [tab_alias]
2. USING { tab_name | view | subquery } [tab_alias]
3. ON ( condition )
4. [ merge_update_clause ]
5. [ merge_insert_clause ]
6. ;

8. condition:
9. valid JOIN condition

11. subquery:

13. merge_update_clause:
14. WHEN MATCHED THEN UPDATE SET
15. column_name = expr [,...]
16. [ WHERE update_condition ]
17. [ DELETE WHERE delete_condition ]

19. merge_insert_clause:
20. WHEN NOT MATCHED THEN INSERT
21. [ ( column_name [,...]  ) ]
22. VALUES ( expr [,...] )
23. [ WHERE insert_condition ]

2. 定义和用法

MERGE INTO 相当于 UPDATE target_tab_name RIGHT OUTER JOIN tab_name ON (condition)，当 target_tab_name 有相应匹配的数据时，就执行 merge_update_clause 子句；若无匹配数据时，则执行 merge_insert_clause 子句。

  • update_condition 是根据更新前的内容来运算。当运算结果为真时，才会更新。
  • delete_condition 是根据更新后的内容来运算。当运算结果为真时，才会刪除。
  • insert_condition 是根据更新前的内容来运算。当运算结果为真时，才会插入新内容。

3. Oracle兼容说明

在原生 UPDATE ... RIGHT OUTER JOIN ON 的基础上，实现 MERGE INTO 语法兼容。但有以下限制：

  • 对象 target_tab_name 必须是基本表，不可以是视图或派生表。
  • 不支持 EXPLAIN。
  • 在触发器（trigger） 内，无法禁止更新ON子句所引用的列。

4. 示例

1. greatsql> CREATE TABLE t1 (
2. id BIGINT(10) PRIMARY KEY,
3. name VARCHAR(16),
4. sale BIGINT(10),
5. operatime BIGINT);

7. greatsql> CREATE TABLE t2(
8. id BIGINT(10),
9. name VARCHAR(16),
10. sale BIGINT(20),
11. UNIQUE KEY `idx_id` (`id`));

13. greatsql> INSERT INTO t1 VALUES(1, 'Cindy', 1000, 1000), (2, 'James',  500, 1000);

15. greatsql> INSERT INTO t2 VALUES(1, 'Cindy',  300), (2, 'James',  400), (3,  'John',  900),(4, 'Peter', 1200);

17. greatsql> SELECT * FROM t1;
18. +----+-------+------+-----------+
19. | id | name  | sale | operatime |
20. +----+-------+------+-----------+
21. |  1 | Cindy | 1000 |      1000 |
22. |  2 | James |  500 |      1000 |
23. +----+-------+------+-----------+
24. 2 rows in set (0.00 sec)

26. greatsql> SELECT * FROM t2;
27. +------+-------+------+
28. | id   | name  | sale |
29. +------+-------+------+
30. |    1 | Cindy |  300 |
31. |    2 | James |  400 |
32. |    3 | John  |  900 |
33. |    4 | Peter | 1200 |
34. +------+-------+------+
35. 4 rows in set (0.00 sec)

37. greatsql> MERGE INTO t1
38. USING t2
39. ON ( t2.id = t1.id )
40. WHEN MATCHED THEN
41. UPDATE SET
42. t1.name = t2.name,
43. t1.sale = t2.sale + t1.id + 20
44. WHERE 1 = 1
45. WHEN NOT MATCHED THEN
46. INSERT
47. VALUES (t2.id, t2.name, t2.sale + t2.id + 10, 1020);
48. Query OK, 4 rows affected (0.00 sec)
49. Rows matched: 2  Changed: 2  Inserted: 2  Deleted: 0  Warnings: 0

51. greatsql> SELECT * FROM t1;
52. +----+-------+------+-----------+
53. | id | name  | sale | operatime |
54. +----+-------+------+-----------+
55. |  1 | Cindy |  321 |      1000 |
56. |  2 | James |  422 |      1000 |
57. |  3 | John  |  913 |      1020 |
58. |  4 | Peter | 1214 |      1020 |
59. +----+-------+------+-----------+

61. greatsql> DELETE FROM t1 WHERE id > 2;

63. greatsql> MERGE INTO t1
64. USING t2
65. ON ( t2.id = t1.id )
66. WHEN MATCHED THEN
67. UPDATE SET
68. t1.name = t2.name,
69. t1.sale = t2.sale + t1.id + 30;
70. Query OK, 2 rows affected (0.00 sec)
71. Rows matched: 2  Changed: 2  Inserted: 0  Deleted: 0  Warnings: 0

73. greatsql> SELECT * FROM t1;
74. +----+-------+------+-----------+
75. | id | name  | sale | operatime |
76. +----+-------+------+-----------+
77. |  1 | Cindy |  331 |      1000 |
78. |  2 | James |  432 |      1000 |
79. +----+-------+------+-----------+

81. greatsql> MERGE INTO t1
82. USING t2
83. ON ( t2.id = t1.id )
84. WHEN MATCHED THEN
85. UPDATE SET
86. t1.name = t2.name,
87. t1.sale = t2.sale + t1.id + 40;
88. Query OK, 2 rows affected (0.00 sec)
89. Rows matched: 2  Changed: 2  Inserted: 0  Deleted: 0  Warnings: 0

91. greatsql> SELECT * FROM t1;
92. +----+-------+------+-----------+
93. | id | name  | sale | operatime |
94. +----+-------+------+-----------+
95. |  1 | Cindy |  341 |      1000 |
96. |  2 | James |  442 |      1000 |
97. +----+-------+------+-----------+

99. greatsql> MERGE INTO t1
100. USING t2
101. ON ( t2.id = t1.id )
102. WHEN NOT MATCHED THEN
103. INSERT
104. VALUES (t2.id, t2.name, t2.sale + t2.id + 10, 3000);
105. Query OK, 2 rows affected (0.00 sec)
106. Rows matched: 0  Changed: 0  Inserted: 2  Deleted: 0  Warnings: 0

108. greatsql> SELECT * FROM t1;
109. +----+-------+------+-----------+
110. | id | name  | sale | operatime |
111. +----+-------+------+-----------+
112. |  1 | Cindy |  341 |      1000 |
113. |  2 | James |  442 |      1000 |
114. |  3 | John  |  913 |      3000 |
115. |  4 | Peter | 1214 |      3000 |
116. +----+-------+------+-----------+
117. 4 rows in set (0.00 sec)

119. greatsql> DELETE FROM t1 WHERE id > 2;
120. Query OK, 2 rows affected (0.00 sec)

122. greatsql> MERGE INTO t1
123. USING t2
124. ON ( t2.id = t1.id )
125. WHEN NOT MATCHED THEN
126. INSERT
127. (t1.id, t1.name, t1.sale, t1.operatime)
128. VALUES
129. (t2.id, t2.name, t2.sale + t2.id + 10, 4000);
130. Query OK, 2 rows affected (0.00 sec)
131. Rows matched: 0  Changed: 0  Inserted: 2  Deleted: 0  Warnings: 0

133. greatsql> SELECT * FROM t1;
134. +----+-------+------+-----------+
135. | id | name  | sale | operatime |
136. +----+-------+------+-----------+
137. |  1 | Cindy |  341 |      1000 |
138. |  2 | James |  442 |      1000 |
139. |  3 | John  |  913 |      4000 |
140. |  4 | Peter | 1214 |      4000 |
141. +----+-------+------+-----------+
142. 4 rows in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx