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)
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
);
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)
