GBase 8a支持Merge 合并功能，混合了update和insert，本文介绍其使用方法。该功能指针对Hash分布表，否则会报错ERROR 1149 (42000): (GBA-02SC-1001) Can't DO Merge when not able to build hash (redistribution) JOIN relationship between merge target and source.
目录导航
语法
Merge into TABLE1 using TABLE2 on ( match condition)
WHEN MATCHED THEN UPDATE_CLUSE
WHEN NOT MATCHED THEN INSERT_CLUSE说明
- match_condition 表关联条件
- UPDATE_CLUSE 更新操作的set 部分
- INSERT_CLUSE 插入操作的字段列表和values值列表部分
改功能，只能用于Hash分布表，且关联条件包含Hash分布列。随机分布表做merge会报错
ERROR 1149 (42000): (GBA-02SC-1001) Can't DO Merge when not able to build hash (redistribution) JOIN relationship between merge target and source.样例
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)