Update table with random values from another table

Поиск
Список
Период
Сортировка
От Rory Campbell-Lange
Тема Update table with random values from another table
Дата
Msg-id 20090212124431.GA13752@campbell-lange.net
обсуждение исходный текст
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Jerry Stuckle
Дата:
Сообщение: Re: Which SQL is the best for servers?
Следующее
От: "Brent Wood"
Дата:
Сообщение: Re: Update table with random values from another table