Set Column to iterate over set of numbers in SnowFlake-CodePudding

I have spent days trying to solve this. I want a column in my snowflake table that iterates over a set of numbers. I can do this easily in Oracle SQL with this code:

alter table <table_name> add col_name numeric(10)
update <table_name> set col_name = mod(rownum,4)

the result is
row 1 col_name = 1
row 2 col_name = 2
row 3 col_name = 3
row 4 col_name = 4
row 5 col_name = 1
row 6 col_name = 2
row 7 col_name = 3
....

I know rownum doesnt exist for snowflake. I am aware of row_number() over (order by..) doesnt work in an update statement. I cannot use NORMAL or UNIFORM, or SEQ. The closest I go was col_name = uniform(1, 4, random()) but this is random. I need it to be ordered.
Cheers!

CodePudding user response：

Write the select statement you want, then replace the table, with the SELECT because that is 100% what the UPDATE is doing, and as a bonus you can order the how you want so the table has good performance.

CREATE OR REPLEACE TABLE foo AS 
SELECT
   a,
   b,
   c,
   ROW_NUMBER() OVER (ORDER BY SEQ4()) % 4 AS col_name
FROM foo
ORDER BY your, clustering, keys