20 October 2018        Add to Favorites   Report

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> [ , ... ] )



Read great educational content like this and a lot more ! Create my free account now 🎁

Copied