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;
