当您需要对Transactional表执行insert、update、delete操作时，可以通过merge into功能将这些操作合并为一条SQL语句，根据与源表关联的结果，对目标Transactional表执行插入、更新或删除操作，提升执行效率。

执行merge into操作前需要具备目标Transactional表的读取表数据权限（Select）及更新表数据权限（Update）。授权操作请参见授权。

功能介绍

MaxCompute支持了delete、update功能，但当您需要使用多个insert、update、delete对目标表进行批量操作时，需要编写多条SQL语句，然后进行多次全表扫描才能完成操作。MaxCompute提供的merge into功能，只需要进行一次全表扫描操作，就可以完成全部操作，执行效率要高于insert+update+delete。

merge into操作具备原子性，作业中的insert、update、delete操作都执行成功时，作业才算执行成功；任一内部逻辑处理失败，则整体作业执行失败。

同时，merge into可以为您避免分别执行insert、update、delete操作时，可能导致部分操作执行成功，部分操作执行失败，其中成功部分无法回退的问题。

使用限制

merge into功能的使用限制如下：
  • 仅支持Transactional表。更多创建Transactional表信息，请参见表操作。
  • 不允许在同一条merge into语句中对相同的行执行多次insert或update操作。

命令格式

merge into <target_table> as <alias_name_t> using <source expression|table_name> as <alias_name_s>
--从on开始对源表和目标表的数据进行关联判断。
on <boolean expression1>
--when matched…then指定on的结果为True的行为。多个when matched…then之间的数据无交集。
when matched [and <boolean expression2>] then update set <set_clause_list>
when matched [and <boolean expression3>] then delete 
--when not matched…then指定on的结果为False的行为。
when not matched [and <boolean expression4>] then insert values <value_list>
  • target_table：必填。目标表名称，必须是实际存在的表。
  • alias_name_t：必填。目标表的别名。
  • source expression|table_name：必填。关联的源表名称、视图或子查询。
  • alias_name_s：必填。关联的源表、视图或子查询的别名。
  • boolean expression1：必填。BOOLEAN类型判断条件，判断结果必须为True或False。
  • boolean expression2、boolean expression3、boolean expression4：可选。update、delete、insert操作相应的BOOLEAN类型判断条件。需要注意的是：
    • 当出现三个WHEN子句时，update、delete、insert都只能出现一次。
    • 如果update和delete同时出现，出现在前的操作必须包括[and <boolean expression>]。
    • when not matched只能出现在最后一个WHEN子句中，并且只支持insert操作。
  • set_clause_list：当出现update操作时必填。待更新数据信息。更多update信息，请参见更新数据（UPDATE）。
  • value_list：当出现insert操作时必填。待插入数据信息。更多values信息，请参见VALUES。

使用示例

创建目标表acid_address_book_base1及源表exists tmp_table1，并插入数据。执行merge into操作，对符合on条件的数据用源表的数据对目标表进行更新操作，对不符合on条件并且源表中满足event_type为I的数据插入目标表。命令示例如下：
--创建目标表acid_address_book_base1。
create table if not exists acid_address_book_base1 
(id bigint,first_name string,last_name string,phone string) 
partitioned by(year string, month string, day string, hour string) 
tblproperties ("transactional"="true"); 

--创建源表exists tmp_table1。
create table if not exists tmp_table1 
(id bigint, first_name string, last_name string, phone string, _event_type_ string);

--向目标表acid_address_book_base1插入测试数据。
insert overwrite table acid_address_book_base1 
partition(year='2020', month='08', day='20', hour='16') 
values (4, 'nihaho', 'li', '222'), (5, 'tahao', 'ha', '333'), 
(7, 'djh', 'hahh', '555');

--向源表exists tmp_table1插入测试数据。
insert overwrite table tmp_table1 values 
(1, 'hh', 'liu', '999', 'I'), (2, 'cc', 'zhang', '888', 'I'),
(3, 'cy', 'zhang', '666', 'I'),(4, 'hh', 'liu', '999', 'U'),
(5, 'cc', 'zhang', '888', 'U'),(6, 'cy', 'zhang', '666', 'U');

--执行merge into操作。
merge into acid_address_book_base1 as t using tmp_table1 as s 
on s.id = t.id and t.year='2020' and t.month='08' and t.day='20' and t.hour='16' 
when matched then update set t.first_name = s.first_name, t.last_name = s.last_name, t.phone = s.phone 
when not matched and (s._event_type_='I') then insert values(s.id, s.first_name, s.last_name,s.phone,'2020','08','20','16');

--查询目标表的数据确认merge into操作结果。
select * from acid_address_book_base1;

+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 5          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
| 1          | hh         | liu        | 999        | 2020       | 08         | 20         | 16         |
| 2          | cc         | zhang      | 888        | 2020       | 08         | 20         | 16         |
| 3          | cy         | zhang      | 666        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+