All Products
Search

This Product

    MaxCompute:MERGE INTO

    Document Center

    MaxCompute:MERGE INTO

    Last Updated:Sep 22, 2026

    When you need to perform INSERT, UPDATE, or 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. Based on the join results with a source table, this statement performs insert, update, or delete operations on the target transactional table and requires only one full table scan, which improves execution efficiency.

    Overview

    MaxCompute supports DELETE and UPDATE operations. However, to perform batch operations on a target table by using multiple INSERT, UPDATE, and DELETE statements, you must write multiple SQL statements and then perform multiple full table scans. The MERGE INTO feature in MaxCompute requires only a single full table scan to complete all operations, and its execution efficiency is higher than that of INSERT+UPDATE+DELETE.

    The MERGE INTO operation is atomic. A job is considered successful only when all its INSERT, UPDATE, and DELETE operations succeed. If any of these operations fail, the entire job fails.

    Additionally, MERGE INTO prevents an issue that can occur when INSERT, UPDATE, and DELETE operations are executed separately, where some operations succeed while others fail, and the successful operations cannot be rolled back.

    Usage notes

    • Before you perform a MERGE INTO operation, you must have the Select and Update permissions on the target transactional table. For more information about how to grant permissions, see MaxCompute permissions.

    • You cannot perform multiple INSERT or UPDATE operations on the same row in a single MERGE INTO statement.

    • The target table for a MERGE INTO operation must be a transactional table.

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

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

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

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

    • Subqueries are not supported in the merge_condition and search_condition.

    Syntax

    MERGE INTO <target_table> AS <alias_name_t>
USING <source expression | table_name> AS <alias_name_s>
--Specifies the join condition to match rows between the source and target tables.
ON merge_condition
{ merge_action } +

merge_action ::= matched_clause | not_matched_by_target_clause | not_matched_by_source_clause

--Specifies the action to perform when rows match based on the ON condition. The data sets processed by multiple WHEN MATCHED clauses cannot overlap.
matched_clause ::= WHEN MATCHED [ AND <search_condition> ] THEN { merge_update_clause | merge_delete_clause }

--Specifies the action to perform when a row in the source does not match any row in the target.
not_matched_by_target_clause ::= WHEN NOT MATCHED [BY TARGET] [ AND <search_condition> ] THEN merge_insert_clause

--Specifies the action to perform when a row in the target does not match any row in the source.
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

    An alias for the target table.

    source expression|table_name

    Yes

    The data source, which can be a table, view, or subquery.

    alias_name_s

    No

    An alias for the source table, view, or subquery.

    merge_condition

    Yes

    A boolean expression.

    search_condition

    No

    A boolean expression that specifies an additional condition for an UPDATE, DELETE, or INSERT action. Note the following rules:

    • When three WHEN clauses are present, UPDATE, DELETE, and INSERT can each appear only once.

    • If UPDATE and DELETE both occur, the first operation must include [AND <BOOLEAN expression>].

    • The WHEN NOT MATCHED clause can appear only as the last WHEN clause.

    set_clause_list

    No

    The data to update. This parameter is required for an UPDATE operation.

    For more information about UPDATE, see UPDATE.

    value_list

    No

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

    For more information about VALUES, see VALUES.

    merge_action

    Yes

    A merge_action can be one of three types: MATCHED, NOT MATCHED BY TARGET, and NOT MATCHED BY SOURCE.

    An action applies to a row only if both the merge_condition and the search_condition are met. If a row satisfies the conditions for multiple clauses, only the first matching merge_action is executed for that row.

    matched_clause

    No

    Defines the UPDATE or DELETE action to perform on a target table row when it matches a source table row.

    not_matched_by_target_clause

    No

    Specifies the action for source rows that do not match any target row.

    not_matched_by_source_clause

    No

    Specifies the action for target rows that do not have a matching source row.

    merge_insert_clause

    No

    The merge_insert_clause has three options: value_list, ROW, and *. value_list specifies the data to insert by using a VALUES expression. ROW and * both indicate that the matching data from the source table is inserted directly into the target table. For ROW and *, the source and target tables must have the same number of columns with compatible data types.

    Examples

    • Example 1: Create the target table acid_address_book_base1 and the source table tmp_table1, and insert data. Execute the MERGE INTO operation to update the target table with data from the source table for records that match the ON condition, and insert data into the target table for records that do not match the ON condition and have an _event_type_ of I in the source table. The following is a sample command:

      --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 sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--Result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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 sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
--Result:
+------------+------------+------------+------------+--------------+
| 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 verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
--Result:
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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. Run a MERGE INTO operation to insert rows from the source table where name is 'Nika', delete rows in the target table where name is 'Tommy', and update rows in the target table where 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 sample 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 verify the result of the insert operation.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 1          | Tommy | 111   |
| 2          | Gene | 222   |
| 3          | Abram | 333   |
+------------+------+-------+

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

--Query the source table to verify the result of the insert operation.
SELECT * FROM tmp_table2;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 6          | Queen | 666   |
| 7          | Nika | 777   |
+------------+------+-------+

--Run the MERGE INTO operation.
--Insert rows from the source table where name is 'Nika' into the target table.
--Delete rows from the target table where name is 'Tommy'.
--Update the phone number from '222' to '999' for rows where name is 'Gene' in the target table.
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 verify the result of the MERGE INTO operation.
SELECT * FROM acid_target1;
--Result:
+------------+------+-------+
| id         | name | phone |
+------------+------+-------+
| 2          | Gene | 999   |
| 7          | Nika | 777   |
| 3          | Abram | 333  |
+------------+------+-------+

    • Example 3: Create the target table merge_acid_dp and the source table merge_acid_source, and insert data. Execute the MERGE INTO command without specifying a partition to update or insert data, which applies 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 sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--Result:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--Insert sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--Result:
+------------+------------+----+----+
| 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 verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
--Result:
+------------+------------+----+----+
| 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 the target table merge_acid_sp and the source table merge_acid_source, and insert data. Execute 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 sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
--Result:
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

--Insert sample 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 verify the result of the insert operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
--Result:
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

--Run the MERGE INTO operation and specify in the ON condition to update or insert data only in the partition where dd = '01' and hh = '01'.
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 verify 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 target table mf_tt6 of the Delta Table type and a source table mf_delta, and insert data. Execute the MERGE INTO command to update, insert, or delete data in a specified partition of the target table. 

      --Create the target table mf_tt6.
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 sample 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. Then, run a SELECT statement to view the data in the mf_tt6 table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--Result:
+------------+------------+----+----+
| 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 sample 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 verify the result of the insert operation.
SELECT * FROM mf_delta;
--Result:
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

--Run the MERGE INTO operation, specifying in the ON condition to update, insert, or delete data only in the partition of mf_tt6 where dd = '01' and hh = '02'.

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 verify the result of the MERGE INTO operation.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
--Result:
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+