Perform INSERT UPDATE and DELETE operations in one statement using MERGE INTO - MaxCompute

MERGE INTO combines INSERT, UPDATE, and DELETE into a single statement against a transactional table or Delta table. The target table is scanned only once, making MERGE INTO more efficient than running the three operations separately.

Common use cases include upserting source data into a target table, applying change data capture (CDC) events, and synchronizing partitions.

Prerequisites

Before you begin, make sure you have:

Select and Update permissions on the target transactional table

For permission setup, see MaxCompute permissions.

How it works

MERGE INTO joins a source table (or subquery) against the target table using the ON condition, then applies each WHEN clause to the join result:

WHEN MATCHED: rows in the target table that match a source row — apply UPDATE or DELETE.
WHEN NOT MATCHED: rows in the source table with no corresponding row in the target table — apply INSERT.

All operations run atomically. If any single operation fails, the entire statement fails and no changes are committed. This is an important advantage over running INSERT, UPDATE, and DELETE as separate statements: if those separate operations run individually, a failure partway through leaves the already-committed changes in place with no way to roll them back.

Limitations

A single MERGE INTO statement cannot perform multiple INSERT or UPDATE operations on the same rows.

Syntax

MERGE INTO <target_table> [AS <alias_name_t>]
USING <source_expression | table_name> [AS <alias_name_s>]
ON <boolean_expression1>
[ matchedClause [ matchedClause ] ]
[ notMatchedClause ]

matchedClause:

WHEN MATCHED [AND <boolean_expression>]
  THEN UPDATE SET <set_clause_list>
  | DELETE

notMatchedClause:

WHEN NOT MATCHED [AND <boolean_expression>]
  THEN INSERT VALUES <value_list>

Parameters

Core parameters

Parameter	Required	Description
`target_table`	Yes	Name of an existing target table
`alias_name_t`	No	Alias for the target table
`source_expression \| table_name`	Yes	Source table, view, or subquery to join with the target table
`alias_name_s`	No	Alias for the source table, view, or subquery
`boolean_expression1`	Yes	Join condition that returns a Boolean value (True or False)

WHEN clause parameters

Parameter	Required	Description
`boolean_expression` (matched)	No	Additional filter applied to the UPDATE or DELETE branch
`boolean_expression` (not matched)	No	Additional filter applied to the INSERT branch
`set_clause_list`	Required for UPDATE	Columns and values to update. See the "UPDATE" section in UPDATE and DELETE
`value_list`	Required for INSERT	Values to insert. See VALUES

Usage notes for WHEN clauses:

A statement can include at most one UPDATE clause, one DELETE clause, and one INSERT clause.
If both UPDATE and DELETE are present, add an AND <boolean_expression> condition to the branch that must be evaluated first to prevent ambiguous row matching.
WHEN NOT MATCHED must be the last WHEN clause and supports only INSERT.

Examples

The following examples cover the most common patterns: upsert, partition-scoped merge, and Delta table merge with conditional UPDATE and DELETE.

Example 1: Upsert rows using an event-type column

This example updates matched rows and inserts unmatched rows where _event_type_ is I (insert event). This pattern is typical for applying CDC events from a staging table.

-- Create a target transactional table partitioned by date and hour.
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 with an event-type column.
CREATE TABLE IF NOT EXISTS tmp_table1
(id BIGINT, first_name STRING, last_name STRING, phone STRING, _event_type_ STRING);

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

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

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

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM tmp_table1;
-- Return results
+------------+------------+------------+------------+--------------+
| 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 MERGE INTO: update matched rows, insert unmatched rows with event type 'I'.
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');

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM acid_address_book_base1;
-- Return results
+------------+------------+------------+------------+------------+------------+------------+------------+
| 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: Merge across all partitions

Without partition constraints in the ON clause, MERGE INTO operates across all partitions of the target table.

-- Create the target table.
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.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

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

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Run MERGE INTO across all partitions.
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);

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_dp;
-- Return results
+------------+------------+----+----+
| 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 3: Merge into a specific partition

Add partition columns to the ON clause to restrict the merge to a specific partition. Rows outside the specified partition are not affected.

-- Create the target table.
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.
CREATE TABLE IF NOT EXISTS merge_acid_source(c1 BIGINT NOT NULL, c2 BIGINT NOT NULL,
  c3 STRING, c4 STRING) lifecycle 30;

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

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_sp;
-- Return results
+------------+------------+----+----+
| c1         | c2         | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 4          | 1          | 02 | 02 |
| 3          | 2          | 02 | 02 |
+------------+------------+----+----+

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

-- Verify the source table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM merge_acid_source;
-- Return results
+------------+------------+----+----+
| c1         | c2         | c3 | c4 |
+------------+------------+----+----+
| 8          | 2          | 03 | 03 |
| 5          | 5          | 05 | 05 |
| 6          | 6          | 02 | 02 |
+------------+------------+----+----+

-- Run MERGE INTO scoped to partition dd='01', 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);

-- Verify the result.
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 4: Conditional UPDATE, DELETE, and INSERT on a Delta table

This example uses a Delta table as the target and applies all three operations in a single statement. An additional condition on the matched branch determines whether a matched row is updated or deleted.

-- Create a Delta table with a primary key.
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");

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

-- Verify the target table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| 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.
CREATE TABLE IF NOT EXISTS mf_delta AS SELECT pk, val FROM VALUES (1, 10), (2, 20), (6, 60) t (pk, val);

-- Verify the source table.
SELECT * FROM mf_delta;
-- Return results
+------+------+
| pk   | val  |
+------+------+
| 1    | 10   |
| 2    | 20   |
| 6    | 60   |
+------+------+

-- Run MERGE INTO scoped to partition dd='01', hh='02'.
-- Matched rows where pk > 1 are updated; matched rows where pk = 1 are deleted.
-- Unmatched rows are inserted into the partition.
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');

-- Verify the result.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_tt6;
-- Return results
+------------+------------+----+----+
| 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 |
+------------+------------+----+----+