MERGE

merge_statement
    : MERGE [INTO] table_name [[AS] table_alias]
        USING table_name [[AS] table_alias]
        join_specification
        merge_operation_spec

merge_operation_spec
    :	{ merge_when_clause }+

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

// This is simplified form of UPDATE. There is no tbl_name and WHERE clause.
merge_matched
    :    UPDATE SET set_clause_list
    |    DELETE

// This is simplified form of INSERT. It inserts only one row.
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 only 3 possible “merge actions” (at least for now):

• 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 “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” will be ignored for “INSERT”, “DELETE” and “UPDATE” actions since there is 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:

MERGE t1 AS Target
USING t2 AS SOURCE
ON (Target.ID = SOURCE.ID AND SOURCE.f1 > 100 ) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(ID, f1) VALUES( S.id, S.f1 )
WHEN MATCHED 
    THEN UPDATE SET T.f1 = S.f1

Update subset of linked records:

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

Delete all not-linked records:

MERGE INTO t_merge_target
USING t_merge_source
ON id = ptr
WHEN NOT MATCHED BY SOURCE THEN 
DELETE