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