Update a column of table with random entry from another table

Question

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

So could a given random value abc appear more than once in the result set? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 4, 2017 at 1:59
@Vao Tsun DATA1 has less rows than test data..but it doesn't matter I just want populate random data from DATA1 to test ..so even if there are 4 rows in DATA1 these must appear randomly in TEST table with how much ever the test columns is filled — Karthic
– Karthic, Commented Aug 4, 2017 at 6:13
Possible duplicate of Populate random data from another table — John Rotenstein
– John Rotenstein, Commented Aug 7, 2017 at 1:27

Laurenz Albe · Accepted Answer · 2017-08-04 06:40:53Z

2

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
           );

answered Aug 4, 2017 at 6:40

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

Karthic Over a year ago

I get an error at distinct (select case when ipreal is not distinct) in postgresql. and I tried using condition instead of distinct WHEN test.abcd <> test.abcd THEN abc -> Query executes but NO Updation

Karthic Over a year ago

sorry about that .. ERROR - >Invalid operation: syntax error at or near "DISTINCT" Position: 70; SET ipreal = (SELECT CASE WHEN ipreal IS NOT DISTINCT FROM ipreal

Laurenz Albe Over a year ago

You must be using something else than plain PostgreSQL then, because I ran the statement above and did not get such an error.

Laurenz Albe Over a year ago

You are kidding, right? 8.0.2? But even this ancient version (install at least the latest point release!) had the IS DISTINCT FROM operator.

Collectives™ on Stack Overflow

Update a column of table with random entry from another table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related