I'm new to PostgreSQL and was trying to implement the following task. Given the column user_id in a PostgreSQL table, I wish to assign random values to the column hub_id in the same table. This hub_id should contain only values available in a list of n numbers - Eg hub_list[25,38,36,300,350].
User_id | Hub_id 1 25 2 36 3 25 4 38How to carry out an update statement for this?
I just tried using this code but it showed only one value throughout the column, and unable to get a list of values
UPDATE <table name>
SET hub_id = (select floor(random() * 10) + 1)
WHERE a.hub_id = a.hub_id;
One method uses arrays:
UPDATE a.user_id
SET hub_id = (array[25, 38, 36, 300, 350])[floor(1 + random()*cardinality(array[25, 38, 36, 300, 350]))];
Here is a db<>fiddle.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments