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 */ 
   ;

CodePudding user response：

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