title: Randomly Assign Rows in Child Table to Row in Parent Table
body: <p>Using MySQL, I am trying to randomly assign rows in a child table to point to a row in a parent table, per following:</p>
  <p>Parent table:  There are 50 WorkGroups (Team Alpha, Team Bravo, etc.), each of which can have a maximum number of WorkEmployees.</p>
  <p>Child table:  There are 2,000 WorkEmployees (Ann, Bob, Carl, etc.) each of whom needs to be assigned to exactly one WorkGroup at random.</p>
  <ul>
  <li>Using MySQL Each WorkEmployee needs to be assigned to exactly one WorkGroup</li>
  <li>Each WorkGroup has a specified maximum number of WorkEmployees it can accommodate</li>
  <li>There are sufficient slots in the WorkGroups to accommodate all WorkEmployees</li>
  <li>I cannot used stored procedures</li>
  </ul>
  <p>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.</p>
  <pre><code>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 ) 
                                           &lt; WorkGroups.MaxMembers ) /* max capacity */
     WHERE WorkEmployees.WorkGroupFK IS NULL /* employee not yet assigned */ 
     ;
  </code></pre>