Subquery in Update statement not working in Snowflake

user_3950587 0
Viewed 133

I am running below query in Snowflake:

UPDATE PROVIDER_XO_SCORE_TABLE AS PXS 
SET PXS.PROVIDER_ID = (SELECT P.PROVIDER_ID 
                       FROM PROVIDER_TABLE P 
                       WHERE PXS.XPI = P.XPI);

This query working fine in MySql but giving below error message in Snowflake.

SQL compilation error:

Unsupported subquery type cannot be evaluated

snowflake-sql snowflake-cloud-data-platform sql-update sql
1 Answers

You can use the join-like syntax with UPDATE...FROM:

UPDATE PROVIDER_XO_SCORE_TABLE PXS 
SET PROVIDER_ID = P.PROVIDER_ID
FROM PROVIDER_TABLE P 
WHERE PXS.XPI = P.XPI;
user_104988280
Related