How to update rows in SnowFlake using a for-loop

Question

I have this table in SnowFlake:

What I want to do is to incrementally update the row_id by internal_id. E.g. for internal_id = CHE20220708134003004472, the row_id should take the values from 1 to 3 respectively, and so on for the other ids.

Here is an example of the desired output:

I've tried to do that by using the following code:

execute immediate $$
declare
  counter integer default 1;
  total_rows integer default (SELECT COUNT(*) FROM "DB_MX_DEV"."STAGING"."stg_chedraui_inv_day" WHERE internall_id = 'CHE20220708134003004472');
begin
  for i in 1 to total_rows do
    counter := counter + 1;
    UPDATE "DB_MX_DEV"."STAGING"."stg_chedraui_inv_day" SET row_id = counter where internall_id = 'CHE20220708134003004472';
  end for;
  return counter;
end;
$$;

However, I got this error: Uncaught exception of type 'STATEMENT_ERROR' on line 8 at position 4 : SQL compilation error: error line 1 at position 65 invalid identifier 'COUNTER'

Note: At the moment, the code above is only trying to update the row_id for a specific internal_id, I'm still trying to figure out how to do it for all ids.

I know the question is asking to do this in a loop - but please, consider doing this NOT on a loop. Do just one large UPDATE statement, and set the ids using ROW_NUMBER() OVER(). — Felipe Hoffa
– Felipe Hoffa, Commented Oct 31, 2022 at 23:12
@FelipeHoffa I actually can't figure out how to do this via an update join, mainly because there is no actual PK in the OP's starting table. If you know of a way, you should post an answer. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 3, 2022 at 15:39

Tim Biegeleisen · Accepted Answer · 2022-10-31 23:23:12Z

2

I suggest not even doing this update. Instead, to view your data this way, just use ROW_NUMBER():

SELECT INTERNALL_ID,
       ROW_NUMBER() OVER (PARTITION BY INTERNALL_ID ORDER BY INTERNALL_ID) ROW_ID
FROM yourTable
ORDER BY 1, 2;

answered Oct 31, 2022 at 23:23

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brenda Over a year ago

Not what I want. I need to do the update.

Felipe Hoffa · Accepted Answer · 2022-11-03 21:35:19Z

0

How about an OVERWRITE to replace all the previous rows, with the rows generated by Tim's solution:

insert OVERWRITE into update_me
select id, row_number() over(partition by id order by row_id) row_id
from update_me

Data setup:

create or replace temp table update_me as

select $1 id, null::number row_id
from values('a'),('a'),('a'),('b'),('b')

answered Nov 3, 2022 at 21:35

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Collectives™ on Stack Overflow

How to update rows in SnowFlake using a for-loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related