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;
Hey, I think that works! Thanks!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
