On Wed, Dec 23, 2015 at 04:32:34AM -0500, Lou Duchez wrote: > >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. I see. As in most programming languages, the () clause is evaluated once and the result used instead. > >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. I'm not aware of a SQL statement to do that. Maybe you will be able do it with a CTE, as you can make a table with a field (1, 2, 3, 4) and take advantage of the integer arithmetic (one problem with your original question is I don't know how to ask for the next employee in plain SQL when the ID is a given name). -- Alberto Cabello Sánchez Universidad de Extremadura -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
