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 support Merge command that can perform update or delete simultaneously.

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.

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

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

Syntax:

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

 

 

Ex:

select * from MERGE_TEST;

 

+---+----+

| A |  B |

|---+----|

| 0 | 10 |

+---+----+

 

select * from MERGE_TEST2;

+---+----+

| A |  B |

|---+----|

| 1 | 11 |

| 2 | 12 |

| 0 | 13 |

+---+----+

Update Table:

Merge into merge_test

Using merge_test2 on merge_test.a = merge_test2.a

When matched then 

Update merge_test Set merge_test.b = merge_test2.b;

O/P:

select * from merge_test;

+---+----+

| A |  B |

|---+----|

| 0 | 13 |

+---+----+

Insert Table:

Update and Insert Table:

Merge into merge_test

Using merge_test2 on merge_test.a = merge_test2.a

When matched then

Update merge_test set merge_test.b = merge_test2.b

When not matched then 

Insert into (a,b) values (merge_test2.a, merge_test2.b);

O/P: 

select * from merge_test;

+---+----+

| A |  B |

|---+----|

| 1 | 11 |

| 2 | 12 |

| 0 | 13 |

+---+----+

Delete and Insert Table:

Merge into merge_test

Using merge_test2 on merge_test.a = merge_test2.a

When matched then

Delete

When not matched then

Insert into (a,b) values (merge_test2.a,merge_test2.b);

O/P: 

select * from merge_test;

+---+----+

| A |  B |

|---+----|

| 1 | 11 |

| 2 | 12 |

+---+----+