Randomly Assign Rows in Child Table to Row in Parent Table

Question

Using MySQL, I am trying to randomly assign rows in a child table to point to a row in a parent table, per following:

Parent table: There are 50 WorkGroups (Team Alpha, Team Bravo, etc.), each of which can have a maximum number of WorkEmployees.

Child table: There are 2,000 WorkEmployees (Ann, Bob, Carl, etc.) each of whom needs to be assigned to exactly one WorkGroup at random.

Using MySQL Each WorkEmployee needs to be assigned to exactly one WorkGroup
Each WorkGroup has a specified maximum number of WorkEmployees it can accommodate
There are sufficient slots in the WorkGroups to accommodate all WorkEmployees
I cannot used stored procedures

Following are the table structures and the UPDATE script I have developed for this purpose. The script is not working. Any guidance on what I have done wrong would be very much appreciated. Thank you.

CREATE TABLE WorkGroups ( ID varchar(64), GroupName varchar(64), MaxMembers int );

CREATE TABLE WorkEmployees ( ID varchar(64), EmployeeName varchar(64), WorkGroupFK varchar(64) );

  UPDATE WorkEmployees
     SET WorkGroupFK = ( SELECT WorkGroups.ID
                           FROM WorkGroups
                          WHERE ( SELECT COUNT(*) 
                                    FROM (SELECT * FROM WorkEmployees) WorkEmployees2
                                   WHERE WorkEmployees2.WorkGroupFK = WorkGroups.ID ) 
                                         < WorkGroups.MaxMembers ) /* max capacity */
   WHERE WorkEmployees.WorkGroupFK IS NULL /* employee not yet assigned */ 
   ;

user_5193536 · Answer

The problem besides, that it tales tome to make sample data, is that you havn't introduced the random factor and that you can only add 1 workgroup to a emplyoee

So adding a ORDER BY RAND() and a 'LIMIT 1 does the trick

CREATE TABLE WorkGroups ( ID varchar(64), GroupName varchar(64), MaxMembers int );

CREATE TABLE WorkEmployees ( ID varchar(64), EmployeeName varchar(64), WorkGroupFK varchar(64) );

INSERT INTO WorkGroups VALUES(1,'test1',2),(2,'test1',3),(3,'test1',2)

INSERT INTO WorkEmployees VALUES (1,'emp1', NULL),(2,'emp2', NULL),(3,'emp3', NULL),(4,'emp4', NULL)
,(5,'emp5', NULL),(6,'emp6', NULL),(7,'emp7', NULL)

  UPDATE WorkEmployees
     SET WorkGroupFK = ( SELECT WorkGroups.ID
                           FROM WorkGroups
                          WHERE ( SELECT COUNT(*) 
                                    FROM (SELECT * FROM WorkEmployees) WorkEmployees2
                                   WHERE WorkEmployees2.WorkGroupFK = WorkGroups.ID ) 
                                         < WorkGroups.MaxMembers 
                      ORDER BY RAND()
                        LIMIT 1) /* max capacity */
   WHERE WorkEmployees.WorkGroupFK IS NULL /* employee not yet assigned */ 
   ;

SELECT * FROM WorkEmployees

ID | EmployeeName | WorkGroupFK
:- | :----------- | :----------
1  | emp1         | 2          
2  | emp2         | 2          
3  | emp3         | 3          
4  | emp4         | 3          
5  | emp5         | 1          
6  | emp6         | 3          
7  | emp7         | 2

db<>fiddle here

Randomly Assign Rows in Child Table to Row in Parent Table

1 Answers