Populate random data from another table

user_7159514 0
Viewed 2409
update dataset1.test
   set column4 = (select column1 
                 from dataset2
                 order by random()
                 limit 1
                 )

I have to update dataset1 of column 4 with each row updating a random entry from dataset 2 column.. But by far now in this above query I get only one random entry in all the rows of dataset1 and its all same which I want it to be random.

amazon-redshift postgresql
2 Answers

Better is to reference the outer table from the subquery. Then the subquery has to be evalued for every row:

update dataset1.test
   set column4 = (select
        case when dataset1.test.column4 = dataset1.test.column4
             then column1 end
        from dataset2
        order by random()
        limit 1
   )
user_19136840
Related