CODEWITHSUNDEEP
postgresql
Karthic
Karthic

Reputation: 115

Update a column of table with random entry from another table

update testdata.test
   set abcd = (select abc 
                 from DATA1
                order by random()
                limit 1
              )

Doing this only makes one random entry from table DATA1 is getting populated in all the rows of TEST table.

What I need is - > to generate each row with random entry from DATA 1 table to TEST table

Upvotes: 0

Views: 1598

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246738

Reference the outer table from the subquery so that it becomes a correlated subquery. Then it has to be executed for every row:

UPDATE testdata.test
SET abcd = (SELECT CASE WHEN test.abcd IS NOT DISTINCT FROM test.abcd
                        THEN abc 
                   END
            FROM data1
            ORDER BY random()
            LIMIT 1
           );

Upvotes: 2

Related Questions