Re: Update table with random values from another table

On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange
<rory(at)campbell-lange(dot)net> wrote:
> 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).

That would be because, for every row in users table, postgres has to
run two subselects, with order by random() (which in it self is quite
expensive).

How many roles it should update ? or otherwise: what's the return of
select count(*) from users where n_role IN (2,3) ?

try that sort of approach (modify it for your use):

test2=# create table foo(a int, b int);

.. insert some test data to foo(), and ziew(a) ...

test2=# update foo set a=n1.a , b=n2.a from (select
generate_series(1,100) id, a from ziew order by random() limit 100)
n1, (select generate_series(1,100) id, a from ziew order by random()
limit 100) n2;

--
GJ