CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Sherin Shaziya
Sherin Shaziya

Reputation: 147

Unsupported subquery type cannot be evaluated - How to fix this?

Update upd_tbl a1
set last_update_date=sysdate(), 
rno=(select rno from sid b1
     where a1.rowid_object=b1.rowid_object
    )
where rowid_object in (select rowid_object from sid);

When I run the above query, I'm getting this error - SQL compilation error: Unsupported subquery type cannot be evaluated.

Kindly guide how to fix this error in Snowflake.

Thanks!

Upvotes: 1

Views: 1718

Answers (1)

Jim Demitriou
Jim Demitriou

Reputation: 603

This should work, however, it should be noted that if you have a many-1 relationship between the rowid_object join condition, this may need to be revisited using exists logic for a boolean test:

https://docs.snowflake.com/en/sql-reference/operators-subquery.html

The following code reflects the pattern used for updating a table based on another table's data:

https://docs.snowflake.com/en/sql-reference/sql/update.html

Update
    upd_tbl a1
set
    a1.last_update_date = sysdate(),
    a1.rno = b1.rno
from sid b1
where a1.rowid_object = b1.rowid_object;

Upvotes: 2

Related Questions