CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowsql
Sumanth Sunny
Sumanth Sunny

Reputation: 13

Merge Function limitation in snowflake

I am trying to convert a merge function that runs in Oracle to Snowflake compatible. When using the merge it is limiting me on the where clause. Please do let me know if any one could help.

Error Message: Image

I am using merge logic with below condition, it throwing error as where clause in unexpected. (Highlighted below with bold)

merge into INV_bas_SAP_ZIBP_SUPPLY b using inv_stg_dev.INV_stg_SAP_ZIBP_SUPPLY s
   on s.SCHEDULE_LINE_DATE = b.SCHEDULE_LINE_DATE
   when matched then
      update set b.col1 = s.col1, b.col2 = s.col2
      where col3=not null
   when not matched then
      insert (col1, col2) values (s.col1, s.col2);

Upvotes: 1

Views: 1731

Answers (2)

BlackJack
BlackJack

Reputation: 149

WHERE condition inside MATCHED statement should be written as :

merge into INV_bas_SAP_ZIBP_SUPPLY b using inv_stg_dev.INV_stg_SAP_ZIBP_SUPPLY s
   on s.SCHEDULE_LINE_DATE = b.SCHEDULE_LINE_DATE
   when matched and col3=not null then
      update set b.col1 = s.col1, b.col2 = s.col2
   when not matched then
      insert (col1, col2) values (s.col1, s.col2);

Upvotes: 0

HIlda Davies
HIlda Davies

Reputation: 161

Please take a look at this URL https://docs.snowflake.net/manuals/sql-reference/sql/merge.html#matchedclause-for-updates-or-deletes

You will need to modify it as follows: when matched and col3 is not null then update set ...

Upvotes: 3

Related Questions