On 23 December 2015 at 16:49, Lou Duchez <lou@xxxxxxxxxxxxx> wrote: > I have a company with four employees who participate in a Secret Santa > program, where each buys a gift for an employee chosen at random. (For > now, I do not mind if an employee ends up buying a gift for himself.) How > can I make this work with an SQL statement? > > Here is the SQL statement I am using to populate the "recipient" column: > > -- > 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 ); > -- > > The problem: every time I run this, a single name is chosen at random and > used to populate all the rows. So all four rows will get a recipient of > "Steve" or "Earl" or whatever single name is chosen at random. Of course: you can't UPDATE a field with a query returning more than one result, as you can check easily trying: update secretsanta set recipient=(select giver from secretsanta); 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. -- 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
