Update table with random values from another table

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Update table with random values from another table
Date: 2009-02-12 12:44:31
Message-ID: 20090212124431.GA13752@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2009-02-12 12:48:53 Re: Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures
Previous Message Gurjeet Singh 2009-02-12 12:19:24 Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures