Re: Update table with random values from another table

On 12/02/09, Rory Campbell-Lange (rory(at)campbell-lange(dot)net) wrote:
> I have a test system for which I need to replace actual user's data (in
> 'users') with anonymised data from another table ('testnames') on
> postgres 8.3.
>
> The tricky aspect is that each row from testnames must be randomised to
> avoid each row in users being updated with the same value.
>
> I've been trying to avoid a correlated subquery for each column I am trying
> to update, and instead trying the following, which never returns. There are
> 2000 records in testnames and about 200 in users.

I'm obviously doing something badly wrong because:

UPDATE
users
SET t_firstname =
(select firstname from testnames order by random() limit 1),
t_surname =
(select lastname from testnames order by random() limit 1)
WHERE
n_role IN (2,3);

Doesn't return either after 60 seconds on a 8 core machine with 8GB of
RAM and 15K disks in R10 (no swap in use).

Rory