CODEWITHSUNDEEP
sqlpostgresqlsql-update
slombo
slombo

Reputation: 99

How to update all rows in a column with random values?

Consider a table named data_records having 5 records and 2 columns (C1 and C2).

The goal is to update all rows of the C2 column with a different random value for each.

The following does not work:

UPDATE data_records SET
  C2 = d.r
FROM
  (SELECT random() as r, generate_series(1, 5) as g) as d

as it update all rows of C2 with the same number.

The subquery

SELECT random() as r, generate_series(1, 5) as g

however, when performed in isolation does generate the table I want, i.e. one random value per row.

How can obtain the behavior I describe?

UPDATE

The reason I am trying to update with a subquery is because I have a case where my target column is of type jsonb. My SET clause looks more like this: 

SET C2 = jsonb_set(C2, '{variation}', to_jsonb(d.r))

Thanks to the comments I realized that my initial question was missing a piece of rationale.

Thanks.

Upvotes: 0

Views: 3035

Answers (3)

S-Man
S-Man

Reputation: 23686

demo: db<>fiddle

The simple one for your JSON is:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(random()));

If you want the second column with the generate_series (for whatever) you will need something to join on the original table. generate_series could give you rows from 1 to 5. So to join on the data_records you would need a 1 to 5 column there too. If this is what is saved in c1 there's no problem. Simply join against c1.

But if not you have to generate it, maybe with a row_number window function which adds the row count as column. Then you are able to join the row count against the generated_series column and you have a row with a random value for each c1 and c2. One of them should be unique. This unique column (c1 in my case) works as the WHERE filter of the UPDATE clause. Of course this could be the c2. But if they are not unique you would end with same random values for same c1/c2 values:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(rand.r))
FROM 
    (SELECT *, row_number() OVER () rn FROM data_records) dr_rn
LEFT JOIN
    (SELECT generate_series(1, 5) gs , random() r) rand 
ON dr_rn.rn = rand.gs

WHERE dr.c1 = dr_rn.c1;

It would be really more simple if you would have an unique id column. But nevertheless I don't see any reasons for making this that complicated.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use just random function no need any sub-query

update t
   set C2 = random()

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this will do what you want:

UPDATE data_records
    SET C2 = random();

I'm not sure why you want to use a subquery or generate_series().

Upvotes: 1

Related Questions