CODEWITHSUNDEEP
sqlpostgresql-9.5
xCloudx8
xCloudx8

Reputation: 721

Update table with random numbers

I need to update my table with random numbers, this is my table:

chr pos ref alt id_disease
chr1 123 A   A     NULL
chr2 456 A   T     NULL

I want to update the disease column with random numbers from 1 to 30000079.

To obtain this:

chr pos ref alt id_disease
chr1 123 A   A     5024
chr2 456 A   T     300045

Is there some random functions?

Upvotes: 2

Views: 4079

Answers (2)

Marcel
Marcel

Reputation: 1280

I ran into the issue that all rows were updated with the same value. This happened because the random statement is only run once. I had to generate the random numbers in a temporary table first, and update the actual table after that:

WITH r AS (SELECT id, floor(random() * 30000079) AS rnd FROM table_name)
UPDATE table_name AS t
SET column_name = r.rnd
FROM r WHERE r.id = t.id;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Use RANDOM():

UPDATE yourTable
SET id_disease = FLOOR(RANDOM() * 30000079) + 1

Explanation

Postgres' RANDOM() function returns a number in the range 0.0 <= x < 1.0. In the query above, this means that the smallest value would occur when RANDOM() returns 0, giving 1 as the value. The highest value would occur when RANDOM() returns something like 0.999999, which would give a value slightly below 30000079. The FLOOR function would take it down to 30000078, but then we add 1 to it to bring it back to 30000079, your highest value.

Upvotes: 6

Related Questions