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
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.