merge into语法

样例

t1 是主表，t2是变动表，要将t2的数据merge到主表T1

环境

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t2;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1212 | 2011-12-12 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
3 rows in set (Elapsed: 00:00:00.01)

Merge

匹配上的id=1被更新了，没匹配上的2和3被insert了。

gbase> merge into t1 using t2 on t1.id=t2.id when matched then update set t1.name=t2.name,t1.birth=t2.birth when not matched then insert(id,name,birth)values(t2.id,t2.name,t2.birth);
Query OK, 3 rows affected (Elapsed: 00:00:00.13)
Rows matched: 3  Changed: 3  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    9 | 9999 | 2011-09-09 |
|    1 | 1212 | 2011-12-12 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.01)

只包含Matched，用于更新

gbase> truncate table t1;
Query OK, 4 rows affected (Elapsed: 00:00:00.21)

gbase> insert into t1 values(1,'1111','2011-11-11'),(9,'9999','2011-09-09');
Query OK, 2 rows affected (Elapsed: 00:00:00.14)
Records: 2  Duplicates: 0  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> merge into t1 using t2 on t1.id=t2.id when matched then update set t1.name=t2.name,t1.birth=t2.birth;                      Query OK, 1 row affected (Elapsed: 00:00:00.17)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    9 | 9999 | 2011-09-09 |
|    1 | 1212 | 2011-12-12 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

只包含NOT Matched用于插入新数据

gbase> truncate table t1;
Query OK, 2 rows affected (Elapsed: 00:00:00.16)

gbase> insert into t1 values(1,'1111','2011-11-11'),(9,'9999','2011-09-09');
Query OK, 2 rows affected (Elapsed: 00:00:00.12)
Records: 2  Duplicates: 0  Warnings: 0

gbase> merge into t1 using t2 on t1.id=t2.id when not matched then insert(id,name,birth)values(t2.id,t2.name,t2.birth);           Query OK, 2 rows affected (Elapsed: 00:00:00.17)
Rows matched: 2  Changed: 2  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.00)