Re: Update table with random values from another table

On Thu, Feb 12, 2009 at 05:39:49PM +0000, Rory Campbell-Lange wrote:
> On 12/02/09, Rory Campbell-Lange (rory(at)campbell-lange(dot)net) wrote:
> > I realise that for every row in my users table (which has a unique
> > integer field) I can update it if I construct a matching id field
> > against a random row from the testnames table.
>
> I can make my join table pretty well by using the ranking procedures
> outlined here: http://www.barik.net/archive/2006/04/30/162447/
>
> CREATE TEMPORARY SEQUENCE rank_seq;
> select nextval('rank_seq') AS id, firstname, lastname from testnames;
[...]
> Any other ideas?

The first is similar to the best I could come up with as well. Your
problem is difficult to express in SQL because what you're trying to do
doesn't seem very relational in nature. I'd do something like:

BEGIN;
ALTER TABLE users ADD COLUMN num SERIAL;
CREATE TEMP SEQUENCE s1;
UPDATE users u SET name = x.name
FROM (
SELECT name, nextval('s1') AS id
FROM (
SELECT name FROM testnames ORDER BY random() OFFSET 0) x) x
WHERE u.id = x.id;
ALTER TABLE users DROP COLUMN num;
COMMIT;

If your existing unique integer field runs from 1 to a number less than
the number of testuser names then you won't need to add the "num" column
first. The inner selects are about making sure that things are ordered
randomly before we assign a sequence value to them, not sure if it's
strictly needed but shouldn't hurt.

--
Sam http://samason.me.uk/