Re: Update table with random values from another table

[Rory Campbell-Lange <rory@xxxxxxxxxxxxxxxxxx>]

On 12/02/09, Rory Campbell-Lange (rory@xxxxxxxxxxxxxxxxxx) 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general