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
