Snowflake UDF - unsupported subquery type

I am trying to create a function that will return a label based on a score and version number in a look up table

so i have a look up table

create function get_label(score int, version int, vector string)
returns varchar
language sql as
$$
select label
from lookup
where version = coalesce(version, to_number(substr(vector,2,1)))
and score between min and max;
$$

I have various tables that I want to set a new column of derived label. some of them have a version and others have a vector that contains the version number when I run an update statement.

alter table my_table
set derived_label = get_label(score,version,null);

alter table my_table2
set derived_label = get_label(score,null,vector);

I get the following error

Unsupported subquery type cannot be evaluated

alter table my_table2
set derived_label = get_label(score,null,vector);

is the same as

create or replace my_table2 as
select * exlcude (derived_label),
   get_label(score,null,vector) as derived_label 
form my_table2;

so having agreed it's a full table rewrite, we can use the replace form (you might want to use some other parameters to make sure there is continity permissions etc).

so now if the create table as select

we can join work on the select:

select m.* exlcude (derived_label),
   l.lookup as derived_label 
form my_table2 as m
left join lookup as l
    /* the get_label(score,null,vector) version */
    on l.version = coalesce(null, to_number(substr(m.vector,2,1)))
     and m.score between l.min and l.max;

I also assumed the m verse l use of parameters and column names.

AND you would want to simplify the coalesce because it is overly complex. Optimizer will edit this though.

AND I assumed you want to values to become NULL when there is not match.. which point you possible should double join, and do only one update/create-replace.

ALSO I would use the CTAS pattern over UPDATE as it allows you to control that sort order, which feeds into the pruning behavour of the table.