SNOWFLAKE Update statement with Join issues

Question

Hello having some odd issues where updating the following with a join statement has some issues that i am unsure of how to use correct syntax to solve as I am new to SNOWFLAKE.

UPDATE ToTable as t2
set t2.val = r.total
from ToTable as t join  
        (select ID, sum(HiddenCash) + sum(Cash) + sum(income) as total
         from  SourceTable
         group by ID)
r on t.ID= r.ID;

Answer 1

If we read the doc's on UPDATE, there is an example that take this form:

update t1
  set number_column = t1.number_column + t2.number_column,
      t1.text_column = 'ASDF'
  from source_table as t2
  where t1.key_column = t2.t1_key and t1.number_column < 10;

which can be written with alias's like you have:

update to_table as t1
  set number_column = t1.number_column + t2.number_column, 
      t1.text_column = 'ASDF'
  from t2
  where t1.key_column = t2.t1_key and t1.number_column < 10;

if we reformat your SQL:

UPDATE ToTable as t2
    set val = r.total
    from ToTable as t 
    join  (
        select ID, 
            sum(HiddenCash) + sum(Cash) + sum(income) as total
        from  SourceTable
        group by ID
    ) as r 
        on t.ID= r.ID;

you have connected your t table to your sub-select results r but nether is connected to your updated target t2.

Given t is the target table, you SQL should drop the meaningless join and join to the real target.

UPDATE ToTable as t2
    set val = r.total
    from (
        select ID, 
            sum(HiddenCash) + sum(Cash) + sum(income) as total
        from  SourceTable
        group by ID
    ) as r 
        on t2.ID= r.ID;

But then t2 without a t looks a touch funny, so I would drop the 2