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 each time, most likely because the subquery is evaluated exactly once and then the main query uses that single result over and 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 recipients has 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 this with an SQL statement? I can certainly write a loop, if that's the only solution. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
