Re: Update table with random values from another table - Mailing list pgsql-general

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