MERGE

The MERGE statement inserts, updates, or deletes values in a table based on values from another table or a subquery.

If a table contains a change log with new, modified, or marked-for-deletion rows, the MERGE statement can be particularly efficient.

It checks for matches between incoming values and the values already present in the target table.

See also DELETE and UPDATE.

Syntax

MERGE INTO target 
    USING source ON join_expr {
        WHEN MATCHED THEN 
            UPDATE SET col_name = expr [, col_name1 = expr1 ...] [where_clause] [DELETE where_clause]}
        | WHEN NOT MATCHED THEN 
             INSERT [(col_name [, ...])] VALUES (expr [, ...])} [where_clause]

Parameters

target: The target table.

---

source: A table or subquery that serves as the source of values for the target table.

---

join_expr: Defines the join condition between target and source.

---

WHEN MATCHED: Defines the action to be taken when values in target and source match.

---

UPDATE SET col_name = expr: Defines the values to be updated in the target table.

col_name: The name of the column in target to be updated using values from source.

expr: Specifies the source values from source.

Multiple col_name = expr expressions can be provided in a single UPDATE SET clause.

---

DELETE: Deletes rows from target that match rows in source.

---

NOT MATCHED: Defines the action to be taken when values in target and source do not match.

---

INSERT: Inserts rows from source into target.

---

INSERT col_name [ , ... ] ... VALUES (expr [ , ... ]) (optional): Specifies which values or expressions (expr) should be inserted into which columns (col_name).

Deterministic and Non-Deterministic Results

The type of results produced by a MERGE statement can be either deterministic or non-deterministic. This is primarily determined by the join_expr clause within the MERGE statement.

If the join_expr clause yields exactly one match in source for each row to be updated in target, it is referred to as a deterministic join condition and leads to deterministic results.

If the join_expr clause yields multiple matches in source for a single row in target, it is considered a non-deterministic join condition, which leads to non-deterministic results. In such cases, it is unclear which matching row will actually be used to update the target, making the result unpredictable.

Example 1 | Simple MERGE Statement

create table  mkw_doku.my_table1 (ord string, col1 integer);
create table  mkw_doku.my_table2 (ord string, col1 integer);

insert into mkw_doku.my_table1 (select 'a' as ord, 10 as col1
                                union all 
                                select 'b' as ord,  20 as col1
                                union all
                                select 'c' as ord,  30 as col1);

insert into mkw_doku.my_table2 (select 'a' as ord,  40 as col1
                                union all
                                select 'b' as ord,  null as col1
                                union all
                                select 'd' as ord,  50 as col1);

merge into mkw_doku.my_table1 mt using mkw_doku.my_table2 mt2 
    on (mt.ord = mt2.ord)  
    when matched then
        update set mt.col1 = mt2.col1
    when not matched then
        insert (ord, col1) values (mt2.ord, mt2.col1) 
;

select * from  mkw_doku.my_table1 order by ord; 

+-----+------+
| ord | col1 |
+-----+------+
| a   | 40   |
| b   | NULL |
| c   | 30   |
| d   | 50   |
+-----+------+