MERGE INTO

When you need to apply INSERT, UPDATE, and DELETE operations to a transactional table or Delta table, running them as separate statements requires multiple full table scans. MERGE INTO combines all three into a single statement and scans the target table only once, reducing execution time and eliminating the risk of partial failure that cannot be rolled back.

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 operation fails, the entire statement rolls back and no changes are committed. This guarantees consistency in a way that separate INSERT, UPDATE, and DELETE statements cannot — a failure mid-way through separate statements leaves already-committed changes with no rollback path.

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         |
+------------+------------+------------+------------+------------+------------+------------+------------+

Note

The WHEN NOT MATCHED AND (s._event_type_='I') condition filters out update-type events (U) from being inserted. Rows with _event_type_='U' that have no matching target row are silently skipped. If your CDC stream contains event types beyond I and U, add explicit filters to each branch to handle them correctly.

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 |
+------------+------------+----+----+

Note

The ON clause joins on c2 only, with no partition filter. This triggers a full table scan across both partitions. For large tables, add partition columns to the ON clause (see Example 3) to limit scan scope and improve performance.

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 |
+------------+------------+----+----+

Note

The partition filter in the ON clause (tar.dd = '01' AND tar.hh = '01') limits the match scope — rows in partition dd='02', hh='02' are not matched. However, unmatched source rows (those with no counterpart in dd='01', hh='01') are still inserted, and their destination partition is determined by the values in value_list. In this example, the new rows land in new partitions (05/05 and 02/02) based on the source values.

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 |
+------------+------------+----+----+

Note

When two WHEN MATCHED clauses are present, they are evaluated in order. The first matching clause wins. In this example, rows with pk > 1 match the first clause and are updated; the remaining matched row (pk = 1) falls through to the second clause and is deleted. Place the more specific condition first.