MERGE

Run insert or update operations on a target table from the results of a join with a source table.

note

Dremio supports reading Apache Iceberg v2 tables with positional deletes. Reading tables with equality deletes is not supported. Writes by Dremio are performed using copy-on-write. Writes using merge-on-read are not supported.

Syntax

MERGE INTO <target_table_name>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  [ AS <target_alias> ]
  USING { ( <select_statement> ) | <source_table_name> [ AS <source_alias> ] }
  ON ( <condition> )
  [ WHEN MATCHED THEN
      UPDATE SET * |
      UPDATE SET <column1_name> = <value1> [, <column2_name> = <value2> ... ] ]
    [ WHEN NOT MATCHED THEN
    INSERT * |
    INSERT (<column_name> [, ...]) VALUES (<expression> [, ...])

note

UPDATE SET * and INSERT * were introduced in version 23.0.

Parameters

<target_table_name> String

The path and name of the table into which you want to merge data.

---

AT { REF[ERENCE] | BRANCH } <reference_name> String   Optional

Only for Nessie sources. Specifies the reference at which you want the table to be merged. When this parameter is omitted, the current branch is used.

REF: Identifies the specific reference.

BRANCH: Identifies the specific branch.

note

If you specify the reference for the target table, you must also specify the reference for the source table.

---

AS <target_alias> String   Optional

The alias of the target table.

---

<select_statement> String   Optional

The SELECT query that returns the data that you want to merge into the target table.

---

<source_table_name> String

The path and name of the table that includes the data that you want to merge into the target table.

---

AS <source_alias> String   Optional

The alias of the source table.

---

ON ( <condition> ) String

Specifies the conditions on which source table joins with the target table to determine whether any rows in the source table match rows in the target table.

---

WHEN MATCHED THEN String   Optional

Specifies that all rows of target table which match the rows returned from the source table due to the search condition are updated according to specified UPDATE SET clause.

---

UPDATE SET * String

Equivalent to UPDATE SET col1 = source.col1 [, col2 = source.col2 ...] for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.

---

UPDATE SET <column_name1> = <value1> [, <column_name2> = <value2> ... ] String

Sets the value of one or more columns. The value can be any expression or sub-query that returns a single value.

---

WHEN NOT MATCHED THEN String   Optional

Specifies that a row is inserted into the target table for every source row matching the search condition that does not match a row in the target table. The values to insert are specified by the INSERT clause.

---

INSERT SET * String

Equivalent to INSERT (column_name1 [, ...]) VALUES (source.col1 [, source.col2 ...]) for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.

---

INSERT [ (<column_name> [, ...]) ] VALUES (expression [, ...]) String

Specifies the values to insert into the columns of a new row of the target table when a source row matching the search condition does not match a row in the target table.

Examples

Example 1

Step 1: Create and load the tables.

CREATE TABLE target_table (ID INTEGER, description VARCHAR);
CREATE TABLE source_table (ID INTEGER, description_1 VARCHAR, description_2 VARCHAR);

INSERT INTO target_table (ID, description ) VALUES
  (1, 'Original value'),
  (2, 'Original value');

INSERT INTO source_table (ID, description_1, description_2) VALUES
  (1, 'Value 1', 'Value 2'),
  (3, 'Value 1', 'Value 2');

Step 2: Run the MERGE statement.

MERGE INTO target_table AS t USING source_table AS s ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET description = s.description_2
  WHEN NOT MATCHED THEN INSERT (id, description) VALUES (s.id, s.description_1);

The resulting content of the target table:

id	description
1	Value 2
2	Original value
3	Value 1

Example 2

Step 1: Create and load the tables.

CREATE TABLE target_table (ID INTEGER, desc_1 VARCHAR, desc_2 VARCHAR);
CREATE TABLE source_table (ID INTEGER, description_1 VARCHAR, description_2 VARCHAR);

INSERT INTO target_table (ID, desc_1, desc_2 ) VALUES
  (1, 'Original value 1', 'Original value 2'),
  (2, 'Original value 1', 'Original value 2');

INSERT INTO source_table (ID, description_1, description_2) VALUES
  (1, 'Value 1', 'Value 2'),
  (3, 'Value A', 'Value B');

Step 2: Run the MERGE statement.

MERGE INTO target_table AS t USING source_table AS s ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *

The resulting content of the target table:

id	desc_1	desc_2
1	Value 1	Value 2
2	Original value 1	Original value 2
3	Value A	Value B