MERGE INTO

Feature description

MaxCompute allows you to execute the DELETE and UPDATE statements on a table to delete data from or update data in the table. If you want to perform multiple operations including INSERT, UPDATE, and DELETE on the table at a time, you must write and execute a statement for each operation. In this case, multiple full table scans are required. To increase efficiency, MaxCompute allows you to execute a MERGE INTO statement to perform multiple operations on a table at a time. In this case, only one full table scan is required. This method is more efficient than separately executing the INSERT, UPDATE, and DELETE statements.

The MERGE INTO statement ensures the atomicity of multiple operations. A job is successful only after all the INSERT, UPDATE, and DELETE operations in the job are successful. If an operation fails, the job also fails.

If you separately execute the INSERT, UPDATE, and DELETE statements, specific operations may fail. The data on which successful operations are performed cannot be restored However, the MERGE INTO statement helps you prevent this issue.

Limits

Syntax

merge into <target_table> as <alias_name_t> using <source expression|table_name> as <alias_name_s>
-- The ON clause specifies the JOIN conditions of the source table and the destination table.
on <boolean expression1>
-- The WHEN MATCHED...THEN clause specifies the operation to be performed when the result of the ON clause is True. The operations of multiple WHEN MATCHED...THEN clauses cannot be performed on the same data.
when matched [and <boolean expression2>] then update set <set_clause_list>
when matched [and <boolean expression3>] then delete 
-- The WHEN MATCHED...THEN clause specifies the operation to be performed when the result of the ON clause is False.
when not matched [and <boolean expression4>] then insert values <value_list>

• target_table: required. Specify the name of the destination table, which must be an existing table.
• alias_name_t: required. Specify the alias of the destination table.
• source expression|table_name: required. Specify the name of the source table, view, or subquery that you want to join with the destination table.
• alias_name_s: required. Specify the alias of the source table, view, or subquery that you want to join with the destination table.
• boolean expression1: required. Specify a condition that returns a value of the BOOLEAN type. The value must be True or False.
• boolean expression2, boolean expression3, and boolean expression4: optional. You can specify a condition for each of the UPDATE, DELETE, and INSERT operations that you want to perform. The condition must return a value of the BOOLEAN type. Take note of the following items:
  • If a MERGE INTO statement has three WHEN clauses, each of the UPDATE, DELETE, and INSERT operations can be used only once.
  • If both the UPDATE and DELETE operations are included in a MERGE INTO statement, you must specify a [and <boolean expression>] condition for the operation that needs to be performed first.
  • The WHEN NOT MATCHED clause can be used only as the last WHEN clause and supports only the INSERT operation.
• set_clause_list: required when you use the UPDATE operation. Specify the data that you want to update. For more information about the UPDATE operation, see UPDATE.
• value_list: required when you use the INSERT operation. Specify the data that you want to insert. For more information about the VALUES statement, see VALUES.

Examples

-- Create a destination table named 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"); 

-- Create a source table named exists tmp_table1.
create table if not exists tmp_table1 
(id bigint, first_name string, last_name string, phone string, _event_type_ string);

-- Insert data into the acid_address_book_base1 table.
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');

-- Insert data into the exists tmp_table1 table.
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');

-- Execute the MERGE INTO statement.
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');

-- Query the acid_address_book_base1 table to check the result of the MERGE INTO operation.
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         |
+------------+------------+------------+------------+------------+------------+------------+------------+