Normally, I would be making this table with one SELECT statement with correlated subqueries, but I can't in Snowflake? Anyway, I want to update all rows in a table, with a special value (9999) for the ones that don't match. In T-SQL, I would do something like

update x
set x.value = coalesce(n.value2, 9999)
from x
left join n
on x.id = n.id

How can I do the same in Snowflake?