Update each row with a random selected value from list

Question

I have a table Users with an integer column named status and i want to select a random value from integer list (10,20, 30) and set to status for each row.

I'm using this:

update "Users" set "status" = i.a from (select a  from (SELECT 30 AS a
UNION ALL SELECT 10 AS a
UNION ALL SELECT 20 AS a) as s order by random() limit 1) i

Result:

id  | status  

 1      20
 2      20
 3      20
 -- other rows have the same status value (20)

score 3 · Accepted Answer · answered Jan 14 '18 at 14:21

The problem is that the from clause is executed only once -- hence you only get one value.

If you didn't have a list, the simplest way would be:

update users
    set status = cast(random() * 3 as int) * 10;

You can try this version:

update Users
    set status = (select a
                  from (values (30), (10), (20) ) v(a)
                  limit 1
                 );

However, I think that Postgres will "optimize" away the subquery and decide to execute it only once. (I consider this a bug, because random() is volatile, but that is another matter.)

If that happens, correlating the subquery to the outer query usually fixes the problem:

update Users
    set status = (select a
                  from (values (30), (10), (20) ) v(a)
                  where users.status <> v.a
                  order by random()
                  limit 1
                 );

score -1 · Answer 2 · answered Jan 14 '18 at 14:28

You need some query to select a random value in the set clause like:

UPDATE Users
SET
Status = (
    SELECT TOP 1 [value]
    FROM (
        SELECT * FROM string_split('10,20,30', ',')
        ORDER BY NEWID()
    )
)

you can look here to see different ways to use random values in different DBS. (This example work for SQL server)

Update each row with a random selected value from list

2 Answers2