Euan Hollidge Euan Hollidge - 27 days ago 6
SQL Question

Select random value for each row

I'm trying to select a new random value from a column in another table for each row of a table I'm updating. I'm getting the random value, however I can't get it to change for each row. Any ideas? Here's the code :

UPDATE srs1.courseedition
SET ta_id = teacherassistant.ta_id
FROM srs1.teacherassistant
WHERE (SELECT ta_id FROM srs1.teacherassistant ORDER BY RANDOM()
LIMIT 1) = teacherassistant.ta_id

Answer Source

My guess is that Postgres is optimizing out the subquery, because it has no dependencies on the outer query. Have you simply considered using a subquery?

UPDATE srs1.courseedition
    SET ta_id = (SELECT ta.ta_id
                 FROM srs1.teacherassistant ta
                 ORDER BY RANDOM()
                 LIMIT 1
                );

I don't think this will fix the problem (smart optimizers, alas). But, if you correlate to the outer query, then it should run each time. Perhaps:

UPDATE srs1.courseedition ce
    SET ta_id = (SELECT ta.ta_id
                 FROM srs1.teacherassistant ta
                 WHERE ce.ta_id IS NULL  -- or something like that
                 ORDER BY RANDOM()
                 LIMIT 1
                );