Set column to randomly chosen value from another table

Question

Consider this:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random() LIMIT 1 OFFSET 0);

My goal: Set dest.dest_col in every row to a randomly picked value out of all the values matched by the subquery.

To accomplish that, I added ORDER BY random() LIMIT 1. But this works by picking a random value once, and then setting it to every row in the dest.dest_col.

I tried adding OFFSET 0 (suggested in other answers as a way to prevent Postgres from optimizing the subquery), but this doesn't seem to have any effect.

Also note that I need this to work on PostgreSQL running on GCP.

score 8 · Accepted Answer · answered Aug 23 '18 at 14:36

The subquery is evaluated only once because it has no dependency on the row to update.

You may add one, just to force the engine to evaluate the subquery for each row. For instance if dest_col is a non-null number:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random()+dest_col LIMIT 1)

Or if it's not a number, any other column that is a number in dest will do, or any other expression involving column(s) of the table to update that will not affect the result but create a dependency.

Set column to randomly chosen value from another table

1 Answers1