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