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 :
SET ta_id = teacherassistant.ta_id
WHERE (SELECT ta_id FROM srs1.teacherassistant ORDER BY RANDOM()
LIMIT 1) = teacherassistant.ta_id
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 );