MERGE INTO

Conditionally updates, deletes, or inserts rows into an Apache Iceberg table. A single statement can combine update, delete, and insert actions.

Synopsis

To conditionally update, delete, or insert rows from an Iceberg table, use the following syntax.

MERGE INTO target_table [ [ AS ]  target_alias ]
USING { source_table | query } [ [ AS ] source_alias ]
ON search_condition
when_clause [...]

The when_clause is one of the following:

WHEN MATCHED [ AND condition ]
    THEN DELETE

WHEN MATCHED [ AND condition ]
    THEN UPDATE SET ( column = expression [, ...] )

WHEN NOT MATCHED [ AND condition ]
    THEN INSERT (column_name[, column_name ...]) VALUES (expression, ...)

MERGE supports an arbitrary number of WHEN clauses with different MATCHED conditions. The condition clauses execute the DELETE, UPDATE or INSERT operation in the first WHEN clause selected by the MATCHED state and the match condition.

For each source row, the WHEN clauses are processed in order. Only the first matching WHEN clause is executed. Subsequent clauses are ignored. A user error is raised when a single target table row matches more than one source row.

If a source row is not matched by any WHEN clause and there is no WHEN NOT MATCHED clause, the source row is ignored.

In WHEN clauses that have UPDATE operations, the column value expressions can refer to any field of the target or the source. In the NOT MATCHED case, the INSERT expressions can refer to any field of the source.

Example

The following example merges rows from the second table into the first table if the rows don't exist in the first table. Note that the columns listed in the VALUES clause must be prefixed by the source table alias. The target columns listed in the INSERT clause must not be so prefixed.

MERGE INTO iceberg_table_sample as ice1
USING iceberg2_table_sample as ice2
ON ice1.col1 = ice2.col1
WHEN NOT MATCHED 
THEN INSERT (col1)
      VALUES (ice2.col1)

For more MERGE INTO examples, see Update Iceberg table data.