CODEWITHSUNDEEP
sqlsqlite
javanoob
javanoob

Reputation: 6410

How to update each row in table with random row from another table?

I am doing some testing and need to populate Employee.DepartmentId with Id column from Department table.

When I do like this, All rows in Employee table have same DepartmentId value. But I want different DepartmentId values, it is ok if there are some duplicates.

UPDATE Employee SET DepartmentId = (SELECT Id FROM Department ORDER BY RANDOM() LIMIT 1);

How can I do this?

Adding more details:

How can I write SQL query so that for each row in Employee, engine goes and fetches a random row from Department table, rather than fetch one random row from Department table and use that for every row in Employee.

Upvotes: 1

Views: 843

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is a problem of over-active optimizers which decide to run the subquery only once.

One solution is to add a correlation clause to the outer query:

UPDATE Employee
    SET DepartmentId = (SELECT d.Id
                        FROM Department d
                        WHERE employee.id is not null
                        ORDER BY RANDOM() LIMIT 1
                       );

This gets around the optimizer.

Here is a db<>fiddle.

Upvotes: 3

Related Questions