Re: Update table with random values from another table

Hi Grzegorz

Many thanks for your reply.

On 12/02/09, Grzegorz Ja??kiewicz (gryzman(at)gmail(dot)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