How can I assign a set of values randomly across the column in SQL?

vignesh kempannan

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        38
How 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;
Gordon Linoff

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.

edited at
0

Comments

0 comments
Login to comment

Related

How to randomly assign values across a data frame

How can I swap the values of a selective column randomly?

How can I assign a set of random values as values to a dicionary?

Create a column and assign values randomly

How can I set refs and assign values to looped elements in ReactJS

How can I assign a set of values to a string or Int value?

How can I Set iMacros to run Randomly?

In Excel, how can I ensure that N randomly selected columns from a named range have unique values across each row?

how can i assign no in column A based on Value in B and if there is any Duplicated values assign the same no.?

How can I filter a dataframe based on (randomly selected) unique values of a column?

How can I randomly assign powers to my heroes?

how can i assign a randomly generated integer to a string in C?

How can I average across the values in one column for which all other columns are identical?

How can I assign values with mypy and @property?

How can I assign List Values to a string?

How can i get middle values in particular column in SQL

How can i show the results which as multiple values in the column in SQL

How can I select the surrounding values into the NULLs of a column in SQL?

How do i set a variable randomly between two values?

How can I select a distinct combination of values across 3 or 4 columns in SQL Server

How can I set sql_mode to a list of values

How can I assign values to a DataFrame based on column-to-value mapping?

How can I conditionally assign values to a column in data.table using a function that contains 'if' statements and the shift function?

How do I assign aggregate values to multiple variables on the same column in SQL?

How to assign values randomly between dataframes

How to get maximum column values across a row in Teradata sql?

How to optimise a SQL query to check for consistency of column values across tables

How can I randomly modify a fraction of the values within a column to a new value based on a condition from another row using R?

How can I randomly generate one of two values for a string?