CODEWITHSUNDEEP
sqlsql-updatesnowflake-cloud-data-platform
marcin2x4
marcin2x4

Reputation: 1429

Update rows limit in Snowflake

I there a way to update values for top-n/limit amount of records in Snowflake?

Sample data, top rows are the ones that need to flaged:

enter image description here

The logic must combine SELECT n FROM limit 200 with SET FLAG = 1

Below syntax is not working:

--option 1
UPDATE TBL_NAME limit 20000
       SET FLAG = 1
--option 2
WITH CTE as (SELECT * FROM TBL_NAME LIMIT 20000)
UPDATE CTE
       SET FLAG = '1'

Upvotes: 1

Views: 4709

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

Based on the existence of a natural key, you could simply join from a sub-query like this:

UPDATE tbl_name t
SET flag = 1
FROM (
  SELECT *
  FROM tbl_name
  LIMIT 20000
  ) x
WHERE t.natural_key = x.natural_key;

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

If you need to update only a limited number of rows, you can try this with seq8():

create or replace temp table many_a 
as 
select 'aaaaaaa' str
from table(generator(rowcount => 100));

update many_a
set str='b'
where seq8()<5

enter image description here

enter image description here

Note: if you're doing this over large amounts of data, seq8() might behave in unexpected ways. https://docs.snowflake.com/en/sql-reference/functions/seq1.html

Upvotes: 2

Related Questions