There are three tables, all fields are filled, except for developers.project_id.

Question: how to fill the field randomly based on existing links?

That is, assign each developer a random project of his company.

  • one
    Give textual scripts for creating these tables and filling them with test data. update Tab1 set xx_id=(select id from tab2 where tab2.company=tab1.company order by rand() limit 1 but this is not exactly postgresovsky syntax. you should try on a test base to make a postgresovsky option, but I think the main idea will remain the same - Mike

1 answer 1

If it is possible with repetitions, it will go like this:

 UPDATE developers SET project_id = ( SELECT id FROM projects WHERE developers.id = developers.id ORDER BY RANDOM() LIMIT 1 );

WHERE developers.id = developers.id needed so that RANDOM() generates a random value for each request. See http://www.simononsoftware.com/problem-with-random-in-postgresql-subselect/ .

If you want the project_id to be unique, the best you can think of is the following:

 WITH d(id, r) AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) FROM developers ), p(id, r) AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY RANDOM()) FROM projects ), d2p(d_id, p_id) AS ( SELECT d.id, p.id FROM d JOIN p ON dr = pr ) UPDATE developers SET project_id = ( SELECT p_id FROM d2p WHERE d_id = id );