CODEWITHSUNDEEP
snowflake-cloud-data-platform
Hannah
Hannah

Reputation: 85

performance concern - update null column based on row_num

I'm populating a 20-column table, and I want to divide & label the records by 60 million. So the value of col20 for 1-60,000,000 records will be '1', 61,000,000-120,000,000 records will be '2', etc.

I'm taking following steps:

  1. populating 19 columns, leave col20 as null
  2. create temp table, store all records
  3. delete all from table
  4. insert into table
select *, 1 + FLOOR( (ROW_NUMBER() OVER (order by col1) - 1)/60000000 ) as col20
from temp

I dont really familiar with Snowflake performance, I just feel that delete all & re-insert looks stupid and not efficient. Is there another way to achieve this? Or maybe its not an issue in snowflake in terms of performance?

updates: I should mean this - the table doesnt have primary key/unique key.

Thanks.

Upvotes: 0

Views: 89

Answers (1)

ElenaG
ElenaG

Reputation: 96

You can simply do an UPDATE on col20 after the data is loaded using the same logic you described above. Is there any reason why update cannot be done?

Upvotes: 0

Related Questions