From: | Lou Duchez <lou(at)paprikash(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Secret Santa List |
Date: | 2015-12-23 03:49:52 |
Message-ID: | 567A19E0.5070408@paprikash.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
I suppose the problem is that the "exists" subquery does not re-evaluate
for each record. How do I prevent this from happening? Can I use a
"lateral" join of some kind, or somehow tell PostgreSQL to not be so
optimized?
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2015-12-23 04:35:31 | Re: Secret Santa List |
Previous Message | oleg yusim | 2015-12-23 02:58:24 | Re: Shared system resources |