If you want to perform the INSERT, UPDATE, and DELETE operations on a transactional table, you can encapsulate the operations in one MERGE INTO statement. Then, you can execute this statement to perform multiple operations on the table based on the join condition with a source table. This feature improves efficiency. The MERGE INTO operation is in public preview. You are not charged for executing the MERGE INTO statement on transactional tables. However, you are charged for executing other query statements on transactional tables. In public preview, Alibaba Cloud does not ensure the usage of this feature in production environments. We recommend that you back up your data in advance.

The Select and Update permissions to read data from and update data in a transactional table are granted. For more information, see Authorize users.

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

The MERGE INTO statement has the following limits:
  • You can execute the MERGE INTO statement only on transactional tables. For more information about transactional tables, see Table operations.
  • You cannot perform multiple INSERT or UPDATE operations on the same rows in a table by using a single MERGE INTO statement.

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 and a source table named exists tmp_table1, and insert data into the tables. Execute the MERGE INTO statement on the destination table. The data entries in the source table that meet the specified ON joint condition are used to update the joined data entries in the destination table. The data entries that do not meet the specified ON join condition and whose value of the event_type column is I are inserted into the destination table. The following statements show an example:
-- 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+