What is Snowflake Merge Command ? How to use it ?

Snowflake

The Snowflake Merge command will allows you to perform merge operations between two tables. The Merge operation includes Insert, Delete, and Update operations on the record in the table based on the second table or a subquery. This can be useful if the second table is a change log that contains new rows (which needs to be inserted), modified rows (which need to be updated), and/or marked rows (which needs to be deleted) in the target table.

The following are the functions performed by Snowflake Merge

  1. Update records when the value is matched.
  2. Insert records when the conditions are not matched.
  3. Delete records when the conditions are matched

The command supports semantics for handling the following two cases:
Values that match : Matched Clause performs Update and Delete operation on the target table when the rows satisfy the condition.(for updates and deletes).
Values that do not match (for inserts). Not Matched Clause performs the Insert operation when the row satisfying conditions are not matched.

Syntax
MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ … ]
Where:
matchedClause ::=
WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> … ] | DELETE } [ … ]
notMatchedClause ::=
WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , … ] ) ] VALUES ( <expr> [ , … ] )

target_table : Specifies the table to merge.
source : Specifies the table or subquery to join with the target table.
join_expr : Specifies the expression on which to join the target table and source.

matchedClause (for updates or deletes)
WHEN MATCHED … THEN UPDATE <col_name> = <expr> | DELETE
Specifies the action to perform when the values match.
AND case_predicate
Optionally specifies an expression which, when true, causes the matching case to be executed.
SET col_name = expr [ … ]
Specifies the column within the target table to be updated or inserted and the corresponding expression for the new column value (can refer to both the target and source relations).

notMatchedClause
WHEN NOT MATCHED … THEN INSERT
AND case_predicate
Optionally specifies an expression which, when true, causes the not-matching case to be executed.
( col_name [ , … ] )
Optionally specifies one or more columns within the target table to be updated or inserted.

Example

merge into emp_target_table using emp_source_table 
on emp_target_table.id = emp_source_table.id
when matched then 
update set emp_target_table.description = emp_source_table.description;
Author: user

Leave a Reply