SnowFlake MERGE update/insert all columns

user_959788 0
Viewed 2684

Does snowflake support updating/inserting all columns with a syntax like UPDATE * or INSERT *

 MERGE INTO events 
 USING updates 
      ON events.eventId = updates.eventId
 WHEN MATCHED THEN 
      UPDATE *
 WHEN NOT MATCHED THEN 
       INSERT *

similar to how Databricks does it: https://docs.databricks.com/spark/latest/spark-sql/language-manual/delta-merge-into.html

Or do we have to list out each column and its value ?

I am getting the error when I try the above

syntax error ... unexpected '*'.

and the docs dont help much: https://docs.snowflake.com/en/sql-reference/sql/merge.html

Thanks,

snowflake-cloud-data-platform upsert merge sql
1 Answers

The UPDATE SET */INSERT * are SQL language extensions(not defined in SQL Standard).

Snowflake does not support that kind of syntax:

MERGE:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] 
  THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ...] 
         | DELETE }[...]

notMatchedClause ::=
  WHEN NOT MATCHED [ AND <case_predicate> ] 
  THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

You could vote for such feature at: https://community.snowflake.com/s/ideas

There is already an item called: "implicit update and insert support for MERGE INTO"

user_50708790
Related