Re: Update table with random values from another table

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;

or

SELECT
firstname, lastname,
(SELECT
count(*)
FROM
testnames t2
WHERE
t2.firstname < t1.firstname) + 2 AS id
FROM
testnames t1
ORDER BY
id;

The second method skips some ids (probably because I haven't got an
integer column in testnames)? It looks like I will have to go for the
first procedure or write a function with a loop and counter.

Any other ideas?

Rory