MERGE INTO

MERGE INTO is not supported for native MatrixOne tables. Its availability depends on the Iceberg write, DML, and delete configuration.

Description

MERGE INTO merges rows from a source relation into a target table. For each row, the ON condition decides whether it is matched:

• WHEN MATCHED THEN UPDATE SET ... updates the matching target rows.

• WHEN MATCHED THEN DELETE deletes the matching target rows.

• WHEN NOT MATCHED THEN INSERT ... VALUES (...) inserts new rows.

Each action clause can include an additional AND <condition> to narrow when it applies. At least one action clause is required.

In MatrixOne, MERGE INTO currently supports only Iceberg table mappings as the merge target. The target table must therefore be an external table backed by a registered Iceberg catalog.

Prerequisites

MERGE INTO currently applies only to Iceberg external table mappings. Before using it:

• Register and configure an Iceberg catalog.

• Configure principal mapping and residency policy.

• Enable Iceberg write operations.

• Enable DML and delete operations.

Iceberg is disabled by default. Enable all write and row-level DML gates on every CN that can execute the statement:

[cn.frontend.iceberg]
enable = true
enable-write = true
enable-dml = true
enable-delete = true

The catalog must also have a principal mapping and residency policy registered with iceberg_register_access, as described in CREATE ICEBERG CATALOG. Define the target table with explicit columns and a compatible non-read_only write_mode before running MERGE INTO.

Syntax

MERGE INTO target [AS alias]
USING source [AS alias]
ON expression
[WHEN MATCHED [AND condition] THEN UPDATE SET column = value [, column = value] ...]
[WHEN MATCHED [AND condition] THEN DELETE]
[WHEN NOT MATCHED [AND condition] THEN INSERT [(column [, column] ...)] VALUES (value [, value] ...)]

Arguments

Argument	Description
target	The Iceberg table mapping to modify.
source	The relation whose rows are compared against the target.
expression	The join condition that determines whether a row is matched.
WHEN MATCHED ... UPDATE	Updates matching target rows. Requires MATCHED.
WHEN MATCHED ... DELETE	Deletes matching target rows. Requires MATCHED.
WHEN NOT MATCHED ... INSERT	Inserts new rows. Requires NOT MATCHED.

Examples

Both iceberg_orders and incoming_orders must be created before running this example. iceberg_orders must be a writable Iceberg external table mapping.

MERGE INTO iceberg_orders AS t
USING incoming_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.status = s.status, t.total = s.total
WHEN NOT MATCHED THEN INSERT (order_id, status, total) VALUES (s.order_id, s.status, s.total);

Constraints

• MERGE INTO does not support a RETURNING clause.

• The target must be an Iceberg table mapping; merging into a native MatrixOne table is not supported.