# MERGE INTO
#### 命令功能
通过MERGE INTO命令，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE或DELETE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部同步工作，执行效率要高于INSERT＋UPDATE。
#### 注意事项
分区表合并需要设置参数spark.sql.forcePartitionPredicatesOnPartitionedTable.enabled为false。
#### 命令格式
**MERGE INTO** *\[database_name.\]table_name* *AS* *target_alias*
**USING** *(sub_query \| tableIdentifier) AS source_alias*
**ON** *\<merge_condition\>*
*\[ WHEN MATCHED \[ AND \<condition\> \] THEN \<matched_action\> \]*
*\[ WHEN MATCHED \[ AND \<condition\> \] THEN \<matched_action\> \]*
*\[ WHEN NOT MATCHED \[ AND \<condition\> \] THEN \<not_matched_action\> \]*
*\<merge_condition\> =A equal bool condition*
*\<matched_action\> =*
*DELETE \|*
*UPDATE SET \* \|*
*UPDATE SET column1 = expression1 \[, column2 = expression2 ...\]*
*\<not_matched_action\> =*
*INSERT \* \|*
*INSERT (column1 \[, column2 ...\]) VALUES (value1 \[, value2 ...\])*
#### 参数描述
表1MERGE INTO参数 
| 参数                 | 描述                            |
|:---|:---|
| database_name      | Database名称，由字母、数字和下划线（_）组成。   |
| table_name         | Database中的表名，由字母、数字和下划线（_）组成。 |
| *bucket_name*      | obs桶名称。                       |
| *tbl_path*         | Delta表在obs桶中的存储位置。            |
| target_alias       | 目标表的别名。                       |
| sub_query          | 子查询。                          |
| source_alias       | 源表或源表达式的别名。                   |
| merge_condition    | 将源表或表达式和目标表关联起来的条件            |
| condition          | 过滤条件，可选。                      |
| matched_action     | 当满足条件时进行Delete或Update操作       |
| not_matched_action | 当不满足条件时进行Insert操作             |
   
#### 所需权限
- SQL权限
  表2MERGE INTO所需权限列表 
  | 权限描述                  |
  |:---|
  | 表的UPDATE权限            |
  | 表的INSERT_INTO_TABLE权限 |
  | 表的DELETE权限            |
     
  

- 细粒度权限：dli:table:update, dli:table:insertIntoTable, dli:table:delete。
- 由LakeFormation提供的元数据服务，权限配置详见LakeFormation文档。
 
#### 示例
- 部分字段更新
  ```
  create table h0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1);
  insert into s0 values(1, 1, 's1', 1),(2, 2, 's2', 2);
  //写法1
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set h0.id = s0.id, h0.comb = s0.comb, price = s0.price * 2;
  //写法2
  merge into h0 using s0
  on h0.id = s0.id
  when matched then update set id = s0.id,
  name = s0.name,
  comb = s0.comb + h0.comb,
  price = s0.price + h0.price;
  ```
  
- 缺省字段更新和插入
  ```
  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into s0 values(1, 2, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  merge into h0 as target
  using (
  select id, comb, name, price, flag from s0
  ) source
  on target.id = source.id
  when matched then update set *
  when not matched then insert *;
  ```
  
- 多条件更新和删除
  ```
  create table h0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/h0';
  create table s0(id int, comb int, name string, price int, flag boolean) using delta location 'obs://bucket_name0/db0/s0';
  insert into h0 values(1, 1, 'h1', 1, false);
  insert into h0 values(2, 2, 'h2', 1, false);
  insert into s0 values(1, 1, 's1', 1, true);
  insert into s0 values(2, 2, 's2', 2, false);
  insert into s0 values(3, 3, 's3', 3, false);
  merge into h0
  using (
  select id, comb, name, price, flag from s0
  ) source
  on h0.id = source.id
  when matched and h0.flag = false then update set id = source.id, comb = h0.comb + source.comb, price = source.price * 2
  when matched and h0.flag = true then delete
  when not matched then insert *;
  ```
  
 
#### 系统响应
可在driver日志和客户端中查看命令运行成功或失败。