MERGE Command syntax in Snowflake

Inserts, updates, and deletes values in a table based on values in a second table or a subquery.

MERGE command supports handling the following cases:

• Values that match (for updates and deletes)
  
• Values that do not match (for inserts)
  

Example:

merge into t1 using t2 on t1.t1key = t2.t2key
    when matched and t2.marked = 1 then delete
    when matched and t2.isnewstatus = 1 then 
    update set val = t2.newval, status = t2.newstatus
    when matched then update set val = t2.newval
    when not matched then insert (val, status) values (t2.newval, t2.newstatus);

Syntax:

MERGE INTO <target_table> USING <source> ON <join_expr> 
WHEN MATCHED [ AND <case_predicate> ] 
THEN { UPDATE | DELETE } SET { <col_name> = <expr> } [ ... ]
WHEN NOT MATCHED [ AND <case_predicate> ] 
THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

nVector

posted on 20 Oct 18