> Of course: you can't UPDATE a field with a query returning more than one
> result, as you can check easily trying:
I understand that, and my query does not return more than one result. The problem is that it returns THE SAME result
eachtime, most likely because the subquery is evaluated exactly once and then the main query uses that single result
overand over.
update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each time, and see that the set of available
recipientshas changed. If "Steve" was picked for the first row, "Steve" shouldn't be available for any subsequent row.
If "Fred" was picked for the second row, neither "Steve" nor "Fred" should be available for any subsequent row.
> You could get a list of givers in no particular order (e. g. "select giver
> from secretsanta order by md5(concat(giver,current_time))") then setting
> each employee as next's employee giver.
As in, write a loop in some programming language to update the table one row at a time, or did you envision a way to do
thiswith an SQL statement? I can certainly write a loop, if that's the only solution.
Thanks!