gbase8a merge使用注意事项

支持Merge语法 示例如下： 

MERGE INTO a USING table_x b ON (a.hash_col = b.hash_col) WHEN MATCHED THEN UPDATE SET a.column = b.column,… WHEN NOT MATCHED THEN INSERT (a.column,…) VALUES (b.column,…) 

注意 

UPDATE 部分不支持 DELETE 子句。 

UPDATE 部分和 INSERT 部分不支持 WHERE 子句。 

UPDATE 部分和 INSERT 部分可以省略，但不可以同时省略，否则报语法错误。 

UPDATE 部分和 INSERT 部分位置不可以颠倒。 

INSERT 的 VALUES 部分不允许使用 MERGE 表。 

UPDATE 或 INSERT 中的列如果出现多次，不会报错，后指定的列生效， 但建议不要依赖于该行为，避免这样使用。 

不允许一对多更新：如果 MERGE 表中的一行与 USING 表中的多行符合 连接条件，则报错。 

MERGE INTO a 语句中，a 表必须是哈希分布表。

 USING table_x b ON (a.hash_col = b.hash_col)这部分中包含 JOIN 条件语 句，JOIN 条件中必须存在 a 表哈希分布列的等值关联条件，且该关联条件 下，哈希分布列必须是物理列的关联，不能是表达式、函数。

 如果主表不是hash表 会报错Can’t DO Merge when not able to build hash (redistribution) JOIN relationship between merge target and source. 

 样例

 示例 ：对 t1 表进行 MERGE 操作

gbase> select * from t1;
+------+-------+------------+---------+
| i    | vc    | d          | dc      |
+------+-------+------------+---------+
|    5 | wto   | 2013-02-03 | 110.210 |
|    1 | one   | 2013-02-03 |  11.210 |
|    3 | one2  | 2013-03-03 |  31.210 |
|   11 | one3  | 2013-08-03 |  41.210 |
|    2 | two   | 2013-04-03 |  12.210 |
|   33 | third | 2013-09-04 |  11.216 |
|    8 | five  | 2013-02-03 | 811.201 |
|   14 | three | 2013-07-22 | 161.218 |
| NULL | first | 2013-02-03 | 311.910 |
+------+-------+------------+---------+
9 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t2;
+------+-------+------------+---------+
| i    | vc    | d          | dc      |
+------+-------+------------+---------+
|    1 | one   | 2013-02-03 |  11.200 |
|   14 | three | 2013-06-22 | 161.218 |
|    2 | two   | 2013-08-03 |  12.810 |
|   13 | one2  | 2013-09-03 |  31.010 |
|   30 | third | 2013-09-04 |  11.216 |
|  110 | one3  | 2013-08-03 |  41.210 |
+------+-------+------------+---------+
6 rows in set (Elapsed: 00:00:00.01)

gbase> MERGE INTO t1 USING t2 ON t1.i=t2.i WHEN MATCHED THEN
    -> UPDATE SET t1.vc=t2.vc WHEN NOT MATCHED THEN
    -> INSERT(t1.i,t1.vc) VALUES(t2.i,t2.vc);
Query OK, 6 rows affected (Elapsed: 00:00:00.15)
Rows matched: 6  Changed: 6  Warnings: 0

gbase> select * from t1;
+------+-------+------------+---------+
| i    | vc    | d          | dc      |
+------+-------+------------+---------+
|    3 | one2  | 2013-03-03 |  31.210 |
|    1 | one   | 2013-02-03 |  11.210 |
|    5 | wto   | 2013-02-03 | 110.210 |
|   13 | one2  | NULL       |    NULL |
|   33 | third | 2013-09-04 |  11.216 |
|    8 | five  | 2013-02-03 | 811.201 |
|    2 | two   | 2013-04-03 |  12.210 |
|   11 | one3  | 2013-08-03 |  41.210 |
|   30 | third | NULL       |    NULL |
|  110 | one3  | NULL       |    NULL |
| NULL | first | 2013-02-03 | 311.910 |
|   14 | three | 2013-07-22 | 161.218 |
+------+-------+------------+---------+
12 rows in set (Elapsed: 00:00:00.01)