Snowflake Merge Statement Syntax, Usage and Examples

In a data warehouse, it is common to manipulate the table data. Sometimes, you have to manipulate the source table using data from another table. Many relational databases such as Netezza supports Merge command that can perform update/delete, or delete simultaneously. In this example, we will check how to manipulate table using Snowflake Merge statement with some example.

Snowflake Merge Statement

The merge command in SQL is a command that allows you to update, delete, or insert into a source table using target table. Based on the matching condition rows from the tables are updated, deleted, or new records are inserted.

If you have a requirement to MERGE two tables (say, source and target), then merge is the command that you are looking for.

The target table can be a view or sub query. Use sub query if you need to use join to merge tables.

Snowflake Merge Command Syntax

The MERGE command in Snowflake is similar to merge statement in other relational databases. Following command is the merge statement syntax in the Snowflake.

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

In the Snowflake merge command, you must specify at least one matching_condition statement to identify the rows that you want to update, insert or delete.

Test Data

We will use following tables to demonstrate Snowflake merge command.

select * from MERGE_TEST;

+---+----+
| A |  B |
|---+----|
| 0 | 10 |
+---+----+

select * from MERGE_TEST2;

+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
| 0 | 13 |
+---+----+

Snowflake Merge Example to Update Table

Following simple merge command demonstrates the update merge_test table using merge_test2 table.

merge into merge_test
  using merge_test2 on merge_test.a = merge_test2.a
  when matched then update 
  set merge_test.b = merge_test2.b;

select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 0 | 13 |
+---+----+

As you can see, value is updated for column A.

Snowflake Merge Example to Update and Insert Into Table

Following simple merge command update matching records and insert new records from merge_test2 into merge_test table.

merge into merge_test
  using merge_test2 on merge_test.a = merge_test2.a
  when matched then 
  update set merge_test.b = merge_test2.b
  when not matched then 
  insert (a, b) values (merge_test2.a, merge_test2.b);

>select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
| 0 | 13 |
+---+----+

Snowflake Merge Example to Delete Matching Rows and Insert new Rows

Following merge command allows you to delete the matching records and insert new records.

merge into merge_test
  using merge_test2 on merge_test.a = merge_test2.a
  when matched then 
  delete
  when not matched then 
  insert (a, b) values (merge_test2.a, merge_test2.b);

select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
| 2 | 12 |
+---+----+

Snowflake Merge Example with Predicates – Condition in Matched Clause

Following merge statement allows you to use use case predicates in merge statement WHEN MATCHED and NOT MATCHED clause.

merge into merge_test
  using merge_test2 on merge_test.a = merge_test2.a
  when matched and merge_test.a <> 1 then
  delete;

select * from merge_test;
+---+----+
| A |  B |
|---+----|
| 1 | 11 |
+---+----+

Snowflake Merge with JOIN Condition – Example

As mentioned earlier, you can use a sub-query in the using clause of the MERGE statement. A sub query can contain multiple joins including LEFT, RIGHT, FULL OUTER JOIN statements.

Following example demonstrates the MERGE statement with JOIN condition.

merge into merge_test
  using (select merge_test.a  from merge_test
join merge_test2
on (merge_test.a = merge_test2.a)) as m2 
on merge_test.a = m2.a
  when matched then 
  update set merge_test.a = 11;

Snowflake Merge Command using WITH Clause (CTE)

Snowflake MERGE command supports sub query, you can use WITH clause or CTE with subquery.

For example, consider following example that allows you to use CTE with MERGE statement.

merge into merge_test
  using (
with cte as (select * from merge_test where a = 0)  
select cte.a  from cte
join merge_test2
on (cte.a = merge_test2.a)) as m2 
on merge_test.a = m2.a
  when matched then 
  update set merge_test.b = 100;