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

Hi Grzegorz

Many thanks for your reply.

On 12/02/09, Grzegorz Ja??kiewicz (gryzman@gmail.com) wrote:
> actually forget about that generate_series() in sub queries, I just
> realized that it won't do.
> I don't have too much time to analyse and find solution, but
> essentially you need to do it like in my example.
>
> See, that's where normalization would help a lot. Ie, having a
> separate table for name, and surname - and than third one to connect
> them into full name.

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.

Something like this:

    UPDATE
        users
    SET ....
    FROM
        (SELECT
            dynamic_id, firstname, lastname
        FROM
            testnames
         ORDER BY
             random()
         ) x
    WHERE
        users.id = x.id;

However I'm not sure how to generate a dynamic_id for testnames. If I
use generate_series() I get a full join, rather than

    1  firstname1 lastname1
    2  firstname2 lastname2

Rory