CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Sanjeev
Sanjeev

Reputation: 135

Update all column rows with random values from another table in Snowflake

I need to update all rows of a column with random values selected from another table. I am trying following query - 

UPDATE TEST_CITY
    SET "CITY" = (SELECT NAME FROM CITY SAMPLE (1 rows))

The subquery gives me a random city when executed separately, but in above case all rows are updated with same value.

I have also tried to select random records by id like following but this also updates all rows with same value - 

UPDATE TEST_CITY
    SET "CITY" = (select c.name
                  from city c
                  where c.id = (SELECT uniform(1, 50, random()))
                 )

This query for example updates all rows with different random values-

UPDATE TEST_CITY
    SET "name" = to_varchar(ABS(MOD(RANDOM(1), 1000000)))

Can I have something equivalent to this when random values are strings and should come from a separate table ?

Upvotes: 3

Views: 2087

Answers (3)

Neeraj Sharma
Neeraj Sharma

Reputation: 133

Below code run for me 

UPDATE TEST_CITY a  SET a.CITY = b.NAME FROM (
SELECT NAME ,row_number()  over (order by random()) AS id from CITY) b;

Upvotes: 0

Sanjeev
Sanjeev

Reputation: 135

Following query worked for me. I have used hash on column name to make the update work on all rows of my column -

UPDATE "TEST_CITY" SET "CITY" = C."NAME" FROM CITY C WHERE C."ID" = ABS(HASH("CITY")%16917) + 1 ;

16197 are the number of rows I have in City table.

Thanks

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270533

I don't know specifically for Snowflake, but other databases sometimes optimize subqueries with a volatile function, resulting in a single value.

One solution that I've seen work is to use a correlated subquery:

UPDATE TEST_CITY
    SET "CITY" = (select c.name
                  from city c
                  where c.id = (SELECT uniform(1, 50, random())) AND
                        test_city.city is not null -- any condition should do
                 );

Although the performance is likely to be worse, perhaps order by will work:

UPDATE TEST_CITY
    SET "CITY" = (select c.name
                  from city c
                  order by random()
                  limit 1
                 );

Upvotes: 1

Related Questions