Lou Duchez schrieb am 23.12.2015 um 04:49:
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?
You can populate the table with a single statement:
with people (name) as (
values ('Frank'), ('Joe'), ('Steve'), ('Earl')
)
insert into secretsanta (giver, recipient)
select distinct on (n1.name) n1.name, n2.name
from people n1
join people n2 on n1.name <> n2.name
order by n1.name;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
