samedi 29 juillet 2017

PostGresql: Copy data from a random row of another table

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://ift.tt/2hcKUJj

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?




Aucun commentaire:

Enregistrer un commentaire