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