Merge Function limitation in snowflake

Question

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);

score 3 · Answer 1 · answered Oct 09 '19 at 12:30

3

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 ...

answered Oct 09 '19 at 12:30

HIlda Davies

141
2

score 0 · Answer 2 · answered Oct 09 '19 at 21:19

0

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);

answered Oct 09 '19 at 21:19

BlackJack

139
3
14

Thanks for the reply BlackJack. Please suggest if I add the where condition in the ON clause of the query would it work in the same as the above one which has been suggested. Query: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 and col3=not null when matched then update set b.col1 = s.col1, b.col2 = s.col2 when not matched then insert (col1, col2) values (s.col1, s.col2); – Sumanth Sunny Oct 10 '19 at 07:52
If you will move WHERE clause condition (col3=not null) to ON clause, WHEN MATCHED functionality will remain same **but it will effect WHEN NOT MATCHED functionality as well.** Currently WHEN NOT MATCHED functionality is superset of records having col3 is NULL but after moving col3 is not null to ON clause, you will get subset of records. Functionality will surely get changed, it depends on business logic how you want to filter the data. – BlackJack Oct 11 '19 at 19:24

Merge Function limitation in snowflake

2 Answers2