CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Alex Dembo
Alex Dembo

Reputation: 350

Snowflake insert on MATCHED or update on NOT MATCHED

Is there a way to insert on MATCHED or update on NOT MATCHED in Snowflake?

The documentation states:

The command supports semantics for handling the following cases:
Values that match (for updates and deletes).
Values that do not match (for inserts).

https://docs.snowflake.com/en/sql-reference/sql/merge.html

But I need both update and insert, and it does not matter on which exactly clause matched or not matched.

Thanks.

====== UPDATE ====== Here is what I am trying to achieve:

merge into target_table using source_table 
    on target_table.id = source_table.id

    when matched and condition = 1 then 
        update set target_table.description = source_table.description

    when matched and condition != 1 then 
        update set target_table.description = source_table.description
        insert <some data>

    when not matched 
        insert <some data>;

Upvotes: 1

Views: 9268

Answers (1)

Alex Dembo
Alex Dembo

Reputation: 350

Solved by creating two streams and two separate merge statements.

merge into target_table using source_table 
    on target_table.id = source_table.id

    when matched and condition = 1 then 
        update set target_table.description = source_table.description

    when matched and condition != 1 then 
        update set target_table.description = source_table.description


merge into target_table using source_table 
    on condition = 1

    when not matched 
        insert <some data>;

Upvotes: 2

Related Questions