MERGE

The MERGE command allows you to perform three actions at the same time, combining INSERT, UPDATE and DELETE in the single command.

merge_statement
    : MERGE [INTO] table_name [[AS] table_alias]  // target table
        USING      table_name [[AS] table_alias]  // source table
        join_specification
        merge_operation_spec

merge_operation_spec
    :	{ merge_when_clause }+

merge_when_clause
    :    WHEN MATCHED THEN merge_matched
    |    WHEN NOT MATCHED
         {    [BY TARGET] THEN merge_not_matched
         |    BY SOURCE   THEN merge_matched
         }

// This is a simplified form of UPDATE. It updates only one row. There are no tbl_name and WHERE clause.
merge_matched
    :    UPDATE SET set_clause_list
    |    DELETE

// This is a simplified form of INSERT. It inserts only one row. There are no tbl_name and WHERE clause.
merge_not_matched
    :    INSERT [ ( column_name_list ) ]
         {    DEFAULT VALUES
         |    VALUES ( values_list )
         }

The MERGE statement is a very powerful feature. In general, it is for merging two tables' data. But there are few nuances, which allow using MERGE in the wide range of scenarios.

There are always:

• two tables - source and target (self-merge is also possible);
• predicate to join that tables;
• one or more action(s).

Valentina joins the tables internally and then, iterates the join result firing appropriate actions for each join-result row.

There are three(3) “merge actions” currently supported by MERGE:

• WHEN MATCHED. Fired when there is a record in target table for current source table's record. It could be “UPDATE” or “DELETE” statements only;
• WHEN NOT MATCHED [BY TARGET]. Fired when there is no record in target table for current source table's record. It could be the “INSERT” statement only;
• WHEN NOT MATCHED BY SOURCE. Fired when there is no record in source table for current target table's record. It could be “UPDATE” or “DELETE” statements only;

Important note - “statement triggers” of Target Table will be ignored for “INSERT”, “DELETE” and “UPDATE” actions since there is the MERGE statement, which can fire up a bunch of different actions.

Insert or update (in case of duplicate) records but for some set of records only:

CREATE TABLE t1 ( id LONG, f1 LONG );
CREATE TABLE t2 ( ptr LONG, f2 LONG );
 
INSERT INTO t1 VALUES (1, 11);
 
INSERT INTO t2 VALUES (1, 111);
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 22);
INSERT INTO t2 VALUES (3, 333);

MERGE t1 AS Tgt
USING t2 AS Src
    ON ( Tgt.id = Src.ptr AND Src.f2 > 100 ) 
WHEN NOT MATCHED BY TARGET THEN 
    INSERT(id, f1) VALUES( ptr, f2 )
WHEN MATCHED THEN
    UPDATE SET f1 = f2

Internal JOIN looks as:

1  11    <->     1  111       // MATCHED,     so UPDATE f1 = f2 happens 
NULL     <->     3  333       // NOT MATCHED, so INSERT (3, 333) happens

SELECT * FROM t1;
-- 1, 111
-- 3, 333

Update subset of linked records:

CREATE TABLE t1 ( id LONG, f1 LONG );
CREATE TABLE t2 ( ptr LONG, f2 LONG );
 
INSERT INTO t1 VALUES (1, 11);
INSERT INTO t1 VALUES (4, 44);
 
INSERT INTO t2 VALUES (1, 111);
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 22);
INSERT INTO t2 VALUES (3, 333);

MERGE t1
USING t2
    ON ( t1.id = t2.ptr AND t2.f2 > 100 ) 
WHEN MATCHED THEN
    UPDATE SET f1 = t2.f2

SELECT * FROM t1;
-- 1, 111
-- 4, 44

Delete all not-linked records:

CREATE TABLE t1 ( id LONG, f1 LONG );
CREATE TABLE t2 ( ptr LONG, f2 LONG );
 
INSERT INTO t1 VALUES (1, 11);
INSERT INTO t1 VALUES (2, 22);
INSERT INTO t1 VALUES (3, 33);
 
INSERT INTO t2 VALUES (1, 111);
INSERT INTO t2 VALUES (1, 1);
INSERT INTO t2 VALUES (3, 333);

MERGE INTO t1
USING t2
    ON id = ptr
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE

SELECT * FROM t1;
-- 1, 11
-- 3, 33