All Products
Search

This Product

    MaxCompute:MERGE INTO

    Document Center

    MaxCompute:MERGE INTO

    Last Updated:Dec 23, 2025

    When you need to perform INSERT, UPDATE, and DELETE operations on a transactional table or a Delta table, you can use the MERGE INTO feature to combine these operations into a single SQL statement. This statement performs inserts, updates, or deletes on the target transactional table based on the results of a join with a source table. This process requires only one full table scan, which improves execution efficiency.

    Introduction

    MaxCompute supports DELETE and UPDATE operations. However, to perform batch INSERT, UPDATE, and DELETE operations on a target table, you must write separate SQL statements. This requires multiple full table scans. The MaxCompute MERGE INTO feature performs all these operations with a single full table scan, which is more efficient than running separate INSERT, UPDATE, and DELETE statements.

    MERGE INTO operations are atomic. A job succeeds only if all its INSERT, UPDATE, and DELETE operations succeed. If any internal operation fails, the entire job fails.

    The MERGE INTO statement also prevents issues that can occur when you run INSERT, UPDATE, and DELETE operations separately. For example, if you run these operations separately and some succeed while others fail, the successful changes cannot be rolled back. The MERGE INTO statement avoids this issue.

    Applicability

    • The MERGE INTO operation requires Select (read) and Update permissions on the target transactional table. For more information, see MaxCompute permissions.

    • Do not perform multiple INSERT or UPDATE operations on the same row within a single MERGE INTO statement.

    • The target table of a MERGE INTO statement must be a transactional table.

    • A MERGE INTO statement must include at least one merge_action.

    • If you use multiple not_matched_by_target_clause clauses, at most one clause can omit the search_condition.

    • If you use multiple not_matched_by_source_clause clauses, at most one clause can omit the search_condition.

    • If you use multiple matched_clause clauses, at most one clause can omit the search_condition.

    • Subqueries are not supported in merge_condition and search_condition.

    Command format

    MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
-- The ON clause specifies the join condition for the source and target tables.
ON merge_condition
{ merge_action } +

merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause

-- The WHEN MATCHED...THEN clause specifies the action for a true result from the ON clause. The data processed by multiple WHEN MATCHED...THEN clauses cannot overlap.
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }

-- The WHEN NOT MATCHED...THEN clause specifies the action for a false result from the ON clause.
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause

not_matched_by_source_clause ::= WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ] THEN  { merge_update_clause | merge_delete_clause }

merge_condition ::= BOOLEAN expression

search_condition ::= BOOLEAN expression

merge_update_clause ::= UPDATE SET <set_clause_list>

merge_delete_clause ::= DELETE

merge_insert_clause ::= INSERT <value_list> | ROW | *

    Parameters

    Parameter

    Required

    Description

    target_table

    Yes

    The name of the target table. The table must exist.

    alias_name_t

    No

    The alias of the target table.

    source expression|table_name

    Yes

    The name of the source table, view, or subquery to join.

    alias_name_s

    No

    The alias of the source table, view, or subquery.

    merge_condition

    Yes

    A conditional expression that returns a BOOLEAN value. The result must be True or False.

    search_condition

    No

    The corresponding BOOLEAN conditional expression for an UPDATE, DELETE, or INSERT operation.

    • When a statement includes three WHEN clauses, the UPDATE, DELETE, and INSERT operations can each appear only once.

    • If both the UPDATE and DELETE operations are present, the operation that appears first must include [AND <BOOLEAN expression>].

    • The WHEN NOT MATCHED clause must be the last WHEN clause.

    set_clause_list

    No

    The data to update. This parameter is required for UPDATE operations.

    For more information about UPDATE, see Update data (UPDATE).

    value_list

    No

    The data to insert. This is required for an INSERT operation.

    For more information about VALUES, see VALUES.

    merge_action

    Yes

    The merge_action has three options: MATCHED, NOT MATCHED BY TARGET, and NOT MATCHED BY SOURCE.

    Each merge_action can include an optional search condition (search_condition). The merge_action executes on a row only when both the merge condition (merge_condition) and the search condition (search_condition) are met. If multiple clauses match, only the first matching merge_action executes for that row.

    matched_clause

    No

    The matched_clause has two options, UPDATE and DELETE, to define how to update or delete a row in the target table when it matches a row from the source table.

    not_matched_by_target_clause

    No

    Defines how to insert a row from the source table into the target table if no matching row exists.

    not_matched_by_source_clause

    No

    Defines how to update or delete a row in the target table when it has no matching row in the source table.

    merge_insert_clause

    No

    The merge_insert_clause has three options: value_list, ROW, and *. The value_list option uses a values expression to specify the data to insert. The ROW and * options directly insert matched data from the source table into the target table. For these options, the target and source tables must have the same number of columns, and the corresponding data types must be compatible.

    Examples

    • Example 1: Create a target table `acid_address_book_base1` and a source table `tmp_table1`, and insert data. Run a MERGE INTO operation. If data meets the ON condition, update the target table with data from the source table. If data does not meet the ON condition and the _event_type_ in the source table is I, insert the data into the target table. The following sample commands are used:

      --Create the target table 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 the source table tmp_table1.
CREATE TABLE IF NOT EXISTS tmp_table1 
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

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

--Query the target table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--The following result is returned:
+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | first_name | last_name  | phone      | year       | month      | day        | hour       |
+------------+------------+------------+------------+------------+------------+------------+------------+
| 4          | nihaho     | li         | 222        | 2020       | 08         | 20         | 16         |
| 5          | tahao      | ha         | 333        | 2020       | 08         | 20         | 16         |
| 7          | djh        | hahh       | 555        | 2020       | 08         | 20         | 16         |
+------------+------------+------------+------------+------------+------------+------------+------------+

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

--Query the source table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
--The following result is returned:
+------------+------------+------------+------------+--------------+
| id         | first_name | last_name  | phone      | _event_type_ |
+------------+------------+------------+------------+--------------+
| 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            |
+------------+------------+------------+------------+--------------+

--Run the merge into operation.
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 target table to confirm the result of the merge into operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--The following result is returned:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

    • Example 2: Create a target table `acid_target1` and a source table `tmp_table2`, and insert data. Run a MERGE INTO operation to perform the following actions: insert a row from the source table into the target table if the row does not match any row in the target table and its `name` is 'Nika'. Delete a row from the target table if the row does not match any row in the source table and its `name` is 'Tommy'. Update a row in the target table if the row does not match any row in the source table and its `name` is 'Gene'.

      --Create the target table acid_target1.
CREATE TABLE IF NOT EXISTS acid_target1 
(id BIGINT, name STRING, phone STRING)  
tblproperties ("transactional"="true"); 

--Create the source table tmp_table2.
CREATE TABLE IF NOT EXISTS tmp_table2 
(id BIGINT, name STRING, phone STRING);

--Insert test data into the target table acid_target1.
INSERT OVERWRITE TABLE acid_target1 
VALUES (1, 'Tommy', '111'), (2, 'Gene', '222'), 
(3, 'Abram', '333');

--Query the target table to confirm the result of the data insertion.
SELECT * FROM acid_target1;
--The following result is returned:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+

--Insert test data into the source table tmp_table2.
INSERT OVERWRITE TABLE tmp_table2 
VALUES (6, 'Queen', '666'), (7, 'Nika', '777');

--Query the source table to confirm the result of the data insertion.
SELECT * FROM tmp_table2;
--The following result is returned:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+

--Run the merge into operation.
--Insert the row where name is 'Nika' from the source table into the target table.
--Delete the row where name is 'Tommy' from the target table.
--Update the row where name is 'Gene' in the target table. Set the phone value from '222' to '999'.
MERGE INTO acid_target1 t
USING tmp_table2 s 
ON t.id = s.id
WHEN NOT MATCHED BY TARGET AND s.name = 'Nika'  THEN
  INSERT (id, name, phone) VALUES(s.id, s.name, s.phone)
WHEN NOT MATCHED BY SOURCE AND t.name = 'Tommy'  THEN
  DELETE
WHEN NOT MATCHED BY SOURCE AND t.name = 'Gene'  THEN
  UPDATE SET t.phone = '999';

--Query the target table to confirm the result of the merge into operation.
SELECT * FROM acid_target1;
--The following result is returned:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

    • Example 3: Create a target table `merge_acid_dp` and a source table `merge_acid_source`, and insert data. Run the MERGE INTO command to update or insert data without specifying any partitions. The operation is applied to all partitions of the target table.

      --Create the target table merge_acid_dp.
CREATE TABLE IF NOT EXISTS merge_acid_dp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--Create the source table merge_acid_source.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--Insert test data into the target table merge_acid_dp.
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_dp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

--Query the target table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--The following result is returned:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--Insert test data into the source table merge_acid_source.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--Query the source table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--The following result is returned:
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--Run the merge into operation.
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_dp tar USING merge_acid_source src 
ON tar.c2 = src.c2 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

--Query the target table to confirm the result of the merge into operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--The following result is returned:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 6          | 6          | 02 | 02 |
| 5          | 5          | 05 | 05 |
| 8          | 2          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
+------------+------------+----+----+

    • Example 4: Create a target table `merge_acid_sp` and a source table `merge_acid_source`, and insert data. Run the MERGE INTO command to update or insert data into a specified partition of the target table.

      --Create the target table merge_acid_sp.
CREATE TABLE IF NOT EXISTS merge_acid_sp(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL)
PARTITIONED BY (dd STRING, hh STRING) tblproperties ("transactional" = "true");
--Create the source table merge_acid_source.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

--Insert test data into the target table merge_acid_sp.
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2);
INSERT OVERWRITE TABLE merge_acid_sp PARTITION (dd='02', hh='02')
VALUES (4, 1), (3, 2);

--Query the target table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
--The following result is returned:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--Insert test data into the source table merge_acid_source.
INSERT OVERWRITE TABLE merge_acid_source VALUES(8, 2, '03', '03'),
(5, 5, '05', '05'), (6, 6, '02', '02');

--Query the source table to confirm the result of the data insertion.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--The following result is returned:
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--Run the merge into operation. In the ON condition, specify that the update or insert operation applies only to the dd = '01' and hh = '01' partition of the target table.
SET odps.sql.allow.fullscan=true;
MERGE INTO merge_acid_sp tar USING merge_acid_source src 
ON tar.c2 = src.c2 AND tar.dd = '01' AND tar.hh = '01' 
WHEN MATCHED THEN 
UPDATE SET tar.c1 = src.c1 
WHEN NOT MATCHED THEN 
INSERT VALUES(src.c1, src.c2, src.c3, src.c4);

--Query the target table to confirm the result of the merge into operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
| 8          | 2          | 01 | 01 |
| 1          | 1          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

    • Example 5: Create a Delta table `mf_tt6` as the target table and a source table `mf_delta`, and insert data. Run the MERGE INTO command with specified partitions to update, insert, or delete data in those partitions. 

      --Create the Transactional table 2.0 mf_tt6 as the target table.
CREATE TABLE IF NOT EXISTS mf_tt6 (pk BIGINT NOT NULL PRIMARY key, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
--Insert test data into the target table mf_tt6.
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='02') VALUES (1, 1), (2, 2), (3, 3);
INSERT OVERWRITE TABLE mf_tt6 PARTITION (dd='01', hh='01') VALUES (1, 10), (2, 20), (3, 30);

--Enable full table scan for the current session only. Run the select statement to view data in the mf_tt6 table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--Create the source table mf_delta and insert test data.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

--Query the source table to confirm the result of the data insertion.
SELECT * FROM mf_delta;
--The following result is returned:
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

--Run the merge into operation. In the ON condition, specify that the update, insert, or delete operation applies only to the dd = '01' and hh = '02' partition of the target table mf_tt6.

MERGE INTO mf_tt6 USING mf_delta 
ON mf_tt6.pk = mf_delta.pk AND mf_tt6.dd='01' AND mf_tt6.hh='02' 
WHEN MATCHED AND (mf_tt6.pk > 1) THEN 
UPDATE SET mf_tt6.val = mf_delta.val 
WHEN MATCHED THEN DELETE 
WHEN NOT MATCHED THEN 
INSERT VALUES (mf_delta.pk, mf_delta.val, '01', '02');         

--Query the target table to confirm the result of the merge into operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 10         | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 2          | 20         | 01 | 01 |
| 3          | 3          | 01 | 02 |
| 6          | 60         | 01 | 02 |
| 2          | 20         | 01 | 02 |
+------------+------------+----+----+