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 my Secret Santa table:
--
create table secretsanta
(giver text,
recipient text,
primary key (giver));
insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'), ('Earl');
--
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.
Why not generate the required results in a SELECT then update from that. row_number() could allow you to generate a random number to each giver, then we can generate another random number and join to each random number. That'll give you a giver and recipient combination.
e.g:
select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from secretsanta) r on g.rn = r.rn
You can then wrap that up in a CTE, something along the lines of:
with cte (giver, recipient) as (
select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from secretsanta) r on g.rn = r.rn
)
update secretsanta set recipient = cte.recipient from cte WHERE cte.giver = secretsanta.giver;
