PostGresql: Copy data from a random row of another table

Question

I have two tables, stuff and nonsense.

create table stuff(
    id serial primary key,
    details varchar,
    data varchar,
    more varchar
);
create table nonsense (
    id serial primary key,
    data varchar,
    more varchar
);

insert into stuff(details) values
    ('one'),('two'),('three'),('four'),('five'),('six');
insert into nonsense(data,more) values
    ('apple','accordion'),('banana','banjo'),('cherry','cor anglais');

See http://sqlfiddle.com/#!17/313fb/1

I would like to copy random values from nonsense to stuff. I can do this for a single value using the answer to my previous question: SQL Server Copy Random data from one table to another:

update stuff
set data=(select data from nonsense where stuff.id=stuff.id
    order by random() limit 1);

However, I would like to copy more than one value (data and more) from the same row, and the sub query won’t let me do that, of course.

I Microsoft SQL, I can use the following:

update stuff
set data=sq.town,more=sq.state
from stuff s outer apply
    (select top 1 * from nonsense where s.id=s.id order by newid()) sq

I have read that PostGresql uses something like LEFT JOIN LATERAL instead of OUTER APPPLY, but simply substituting doesn’t work for me.

How can I update with multiple values from a random row of another table?

Nick Barnes · Accepted Answer · 2017-07-30 01:37:33Z

4

As of Postgres 9.5, you can assign multiple columns from a subquery:

update stuff
set (data, more) = (
  select data, more
  from nonsense
  where stuff.id=stuff.id
  order by random()
  limit 1
);

answered Jul 30, 2017 at 1:37

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostGresql: Copy data from a random row of another table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related