Snowflake关联更新SQL报错'missing SET at FROM'问题排查求助

To resolve the syntax error, you need to use Spark SQL's MERGE INTO syntax instead of Snowflake's UPDATE ... FROM approach, since spark.sql() uses Spark's SQL dialect (not Snowflake's native syntax).

Replace your update query with this:

MERGE INTO FINAL_TBL x
USING STG_TBL y
ON y.customer_id = x.customer_id AND y.end_date = x.end_date
WHEN MATCHED THEN UPDATE SET x.NAME = y.NAME

Why the Original Query Failed #

Spark SQL does not support the Snowflake-specific UPDATE ... FROM syntax. Your initial attempt to replace FROM with USING still kept the UPDATE keyword, which is invalid in Spark for join-based updates.

MERGE INTO is the standard Spark SQL construct for performing updates (and inserts/deletes) based on a join between two tables, aligning with both Spark's syntax and Snowflake's supported operations when using the Spark connector.

Alternative: Execute Snowflake Native Syntax Directly #

If you prefer to use Snowflake's original UPDATE ... FROM syntax, you can execute it directly via the Snowflake Python connector (bypassing Spark SQL):

import snowflake.connector

# Establish connection
conn = snowflake.connector.connect(
    user='your_user',
    password='your_password',
    account='your_account',
    warehouse='your_warehouse',
    database='your_db',
    schema='your_schema'
)

# Execute the query
cursor = conn.cursor()
update = """
UPDATE FINAL_TBL x
FROM STG_TBL y
SET x.NAME = y.NAME
WHERE y.customer_id = x.customer_id AND y.end_date = x.end_date
"""
cursor.execute(update)
conn.commit()

# Close connections
cursor.close()
conn.close()

This approach runs the query directly on Snowflake's engine instead of processing it through Spark.

内容的提问来源于stack exchange，提问作者Rigori67