Re: Update table with random values from another table

["Brent Wood" <b.wood@xxxxxxxxxx>]

I'm not sure if that query will do what you want, but to make it work, one thing you might try, is to pre calculate the random values for each record, then order by those, eg: 

select trip_code, random() as rand from obs order by rand;

works for me, so the following might for you:

:
      UPDATE
                users                                           
        SET     
            t_firstname = x.firstname,
            t_surname   = x.lastname,
            t_username  = x.username,
            t_contact   = x.email  
        FROM                    
            (select firstname, lastname, username, email, random() as rand
             from testnames order by rand) 
        WHERE
            t_firstname <> x.firstname;


Cheers,

  Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Rory Campbell-Lange <rory@xxxxxxxxxxxxxxxxxx> 02/17/09 4:33 PM >>>
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. 

       UPDATE
                users                                           
        SET     
            t_firstname = x.firstname,
            t_surname   = x.lastname,
            t_username  = x.username,
            t_contact   = x.email   
        FROM                    
            (select firstname, lastname, username, email
             from testnames order by random()) x
        WHERE
            t_firstname <> x.firstname;


Any help much appreciated
Rory 



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

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

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