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
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
orUPDATE
operations on the same rows in a table by using a singleMERGE 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
, andINSERT
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
, andINSERT
operations can be used only once. - If both the
UPDATE
andDELETE
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 theINSERT
operation.
- If a MERGE INTO statement has three WHEN clauses, each of the
- set_clause_list: required when you use the
UPDATE
operation. Specify the data that you want to update. For more information about theUPDATE
operation, see UPDATE. - value_list: required when you use the
INSERT
operation. Specify the data that you want to insert. For more information about theVALUES
statement, see VALUES.
Examples
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 |
+------------+------------+------------+------------+------------+------------+------------+------------+