§ Oracle兼容-语法-MERGE INTO
§ 1. 语法
MERGE INTO target_tab_name [tab_alias]
USING { tab_name | view | subquery } [tab_alias]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
;
condition:
valid JOIN condition
subquery:
merge_update_clause:
WHEN MATCHED THEN UPDATE SET
column_name = expr [,...]
[ WHERE update_condition ]
[ DELETE WHERE delete_condition ]
merge_insert_clause:
WHEN NOT MATCHED THEN INSERT
[ ( column_name [,...] ) ]
VALUES ( expr [,...] )
[ 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. 示例
greatsql> CREATE TABLE t1 (
id BIGINT(10) PRIMARY KEY,
name VARCHAR(16),
sale BIGINT(10),
operatime BIGINT);
greatsql> CREATE TABLE t2(
id BIGINT(10),
name VARCHAR(16),
sale BIGINT(20),
UNIQUE KEY `idx_id` (`id`));
greatsql> INSERT INTO t1 VALUES(1, 'Cindy', 1000, 1000), (2, 'James', 500, 1000);
greatsql> INSERT INTO t2 VALUES(1, 'Cindy', 300), (2, 'James', 400), (3, 'John', 900),(4, 'Peter', 1200);
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 1000 | 1000 |
| 2 | James | 500 | 1000 |
+----+-------+------+-----------+
2 rows in set (0.00 sec)
greatsql> SELECT * FROM t2;
+------+-------+------+
| id | name | sale |
+------+-------+------+
| 1 | Cindy | 300 |
| 2 | James | 400 |
| 3 | John | 900 |
| 4 | Peter | 1200 |
+------+-------+------+
4 rows in set (0.00 sec)
greatsql> MERGE INTO t1
USING t2
ON ( t2.id = t1.id )
WHEN MATCHED THEN
UPDATE SET
t1.name = t2.name,
t1.sale = t2.sale + t1.id + 20
WHERE 1 = 1
WHEN NOT MATCHED THEN
INSERT
VALUES (t2.id, t2.name, t2.sale + t2.id + 10, 1020);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Inserted: 2 Deleted: 0 Warnings: 0
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 321 | 1000 |
| 2 | James | 422 | 1000 |
| 3 | John | 913 | 1020 |
| 4 | Peter | 1214 | 1020 |
+----+-------+------+-----------+
greatsql> DELETE FROM t1 WHERE id > 2;
greatsql> MERGE INTO t1
USING t2
ON ( t2.id = t1.id )
WHEN MATCHED THEN
UPDATE SET
t1.name = t2.name,
t1.sale = t2.sale + t1.id + 30;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Inserted: 0 Deleted: 0 Warnings: 0
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 331 | 1000 |
| 2 | James | 432 | 1000 |
+----+-------+------+-----------+
greatsql> MERGE INTO t1
USING t2
ON ( t2.id = t1.id )
WHEN MATCHED THEN
UPDATE SET
t1.name = t2.name,
t1.sale = t2.sale + t1.id + 40;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Inserted: 0 Deleted: 0 Warnings: 0
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 341 | 1000 |
| 2 | James | 442 | 1000 |
+----+-------+------+-----------+
greatsql> MERGE INTO t1
USING t2
ON ( t2.id = t1.id )
WHEN NOT MATCHED THEN
INSERT
VALUES (t2.id, t2.name, t2.sale + t2.id + 10, 3000);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Inserted: 2 Deleted: 0 Warnings: 0
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 341 | 1000 |
| 2 | James | 442 | 1000 |
| 3 | John | 913 | 3000 |
| 4 | Peter | 1214 | 3000 |
+----+-------+------+-----------+
4 rows in set (0.00 sec)
greatsql> DELETE FROM t1 WHERE id > 2;
Query OK, 2 rows affected (0.00 sec)
greatsql> MERGE INTO t1
USING t2
ON ( t2.id = t1.id )
WHEN NOT MATCHED THEN
INSERT
(t1.id, t1.name, t1.sale, t1.operatime)
VALUES
(t2.id, t2.name, t2.sale + t2.id + 10, 4000);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Inserted: 2 Deleted: 0 Warnings: 0
greatsql> SELECT * FROM t1;
+----+-------+------+-----------+
| id | name | sale | operatime |
+----+-------+------+-----------+
| 1 | Cindy | 341 | 1000 |
| 2 | James | 442 | 1000 |
| 3 | John | 913 | 4000 |
| 4 | Peter | 1214 | 4000 |
+----+-------+------+-----------+
4 rows in set (0.00 sec)
§ 问题反馈
§ 联系我们
扫码关注微信公众号
