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" (LPAREN! column_name_list RPAREN! )?
    	(	"default" "values"!
		|	"values"! LPAREN! values_list RPAREN!

The MERGE statement is very powerful feature. In general, it is for merging two tables' data. But there are few nuances which allow to use MERGE in 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