Is it possible to update rows randomly with a group of set values?

Question

I have a work assignment table that I would like help with. What I would like to do is randomly assign peoples names to the rows in the table. For example, the table currently looks like:

        TASK     |NAME
  1 Get Chicken  |
  2 Clean Chicken|
  3 Cook Chicken |
  4 Eat Chicken  |
  5 Eat Corn     |
  6 Takeout Trash|

I have 4 employees that I want to assign these tasks to, but do not want to show any favoritism. Here is what that table looks like:

NAME
John 
Lucy 
Fred 
Jasmine

How can I randomly update the NAME field based on the above names?

edit based on comments. I changed the number of tasks to something not divisible by 4. In this case the number of tasks is now 6. I want to make it to where no one can get 2 or more tasks more then the rest of their colleagues. But in this case, it's ok for someone to have 1 more task then their colleagues. he result should be something like (but random):

        TASK     |NAME
  1 Get Chicken  |John 
  2 Clean Chicken|Jasmine
  3 Cook Chicken |Lucy 
  4 Eat Chicken  |Fred 
  5 Eat Corn     |Fred
  6 Takeout Trash|Jasmine

Are there only 2 columns in assignment table? Are employees names stored in employee table? — Ankit Bajpai, Sep 17 '19 at 18:47
not yet; i will update the original to produce a table w/ the names. — user7002207, Sep 17 '19 at 18:56
Pure random assignment would mean that each employee has a 25% chance of getting each assignment. Which means that it would be entirely possible that one employee would get 2 or 3 assignments and another would get 0. I'm guessing that you want to add additional rules such as that each employee must be assigned to exactly one task. — Justin Cave, Sep 17 '19 at 18:57
@JustinCave, I guess OP needs exactly that. 1 task will assign to only 1 employee nbut the order must be completely random. — Ankit Bajpai, Sep 17 '19 at 19:01
going to edit my question based on these, but what if the number of tasks isnt divisble by 4? for example if there were 6 tasks. I'd like the end result to be where they would be at the most 1 task difference between the workers. — user7002207, Sep 17 '19 at 19:04
order by a random number. assign sequentially https://stackoverflow.com/questions/9868409/how-to-get-records-randomly-from-the-oracle-database — Hogan, Sep 17 '19 at 19:13

score 5 · Accepted Answer · answered Sep 17 '19 at 21:35

Here is a pure SQL way to do it.

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

It sorts each list randomly and assigned a number to each row in each list. It then gets the row from the employee list whose number matched the task number MOD the total number of employees.

Here is a fully example:

Create tables

CREATE TABLE so_tasks 
  ( task VARCHAR2(30) NOT NULL PRIMARY KEY,
    name VARCHAR2(30) );

INSERT INTO so_tasks ( task ) VALUES ('Get Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Clean Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Cook Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Chicken');
INSERT INTO so_tasks ( task ) VALUES ('Eat Corn');
INSERT INTO so_tasks ( task ) VALUES ('Takeout Trash');


CREATE TABLE so_employees 
  ( name  VARCHAR2(30) NOT NULL PRIMARY KEY );

INSERT INTO so_employees ( name ) VALUES ('John');
INSERT INTO so_employees ( name ) VALUES ('Lucy');
INSERT INTO so_employees ( name ) VALUES ('Fred');
INSERT INTO so_employees ( name ) VALUES ('Jasmine');

COMMIT;

Merge

MERGE INTO so_tasks t USING (
WITH numbered_tasks AS ( SELECT t.*, 
                                row_number() OVER (ORDER BY dbms_random.value) task_number, 
                                count(*) OVER () total_tasks FROM so_tasks t ),
     numbered_employees AS ( SELECT e.*, 
                             row_number() OVER (ORDER BY dbms_random.value) employee_number, 
                             count(*) OVER () total_employees FROM so_employees e)
SELECT     nt.task, 
           ne.name
FROM       numbered_tasks nt 
INNER JOIN numbered_employees ne 
      ON ne.employee_number-1 = mod(nt.task_number-1, ne.total_employees) ) u
ON ( t.task = u.task ) 
WHEN MATCHED THEN UPDATE SET t.name = u.name;

Results

SELECT * FROM so_tasks;

+---------------+---------+
|     TASK      |  NAME   |
+---------------+---------+
| Get Chicken   | John    |
| Clean Chicken | Jasmine |
| Cook Chicken  | Lucy    |
| Eat Chicken   | Fred    |
| Eat Corn      | Jasmine |
| Takeout Trash | Fred    |
+---------------+---------+

Your exact assignments for each task will be different, but there will never be more than a one task difference between any two employees.

score 2 · Answer 2 · answered Sep 17 '19 at 21:41

You can give the tasks random sequential numbers and the employees another random sequential number and then join the two tables using those numbers and then use a MERGE statement to update the table correlating on the ROWID pseudo-column to uniquely identify each task.

Oracle Setup:

CREATE TABLE table_name ( task VARCHAR2(20), name VARCHAR2(20) ); 

INSERT INTO table_name ( TASK )
  SELECT 'Get Chicken'   FROM DUAL UNION ALL
  SELECT 'Clean Chicken' FROM DUAL UNION ALL
  SELECT 'Cook Chicken'  FROM DUAL UNION ALL
  SELECT 'Eat Chicken'   FROM DUAL UNION ALL
  SELECT 'Eat Corn'      FROM DUAL UNION ALL
  SELECT 'Takeout Trash' FROM DUAL;

CREATE TABLE employees ( NAME ) AS
  SELECT 'John'    FROM DUAL UNION ALL
  SELECT 'Lucy'    FROM DUAL UNION ALL
  SELECT 'Fred'    FROM DUAL UNION ALL
  SELECT 'Jasmine' FROM DUAL;

Merge:

MERGE INTO table_name dst
USING (
  WITH random_tasks ( rid, rn ) AS (
    SELECT ROWID,
           ROW_NUMBER() OVER ( ORDER BY DBMS_RANDOM.VALUE )
    FROM   table_name
  ),
  random_names ( name, rn, num_employees ) AS (
    SELECT name,
           ROW_NUMBER() OVER ( ORDER BY DBMS_RANDOM.VALUE ),
           COUNT(*) OVER ()
    FROM   employees
  )
  SELECT rid,
         name
  FROM   random_tasks t
         INNER JOIN
         random_names n
         ON ( MOD( t.rn, n.num_employees ) + 1 = n.rn )
) src
ON ( src.rid = dst.ROWID )
WHEN MATCHED THEN
  UPDATE SET name = src.name;

Result:

SELECT * FROM table_name;

TASK          | NAME   
:------------ | :------
Get Chicken   | John   
Clean Chicken | Jasmine
Cook Chicken  | Fred   
Eat Chicken   | Lucy   
Eat Corn      | Fred   
Takeout Trash | Lucy

db<>fiddle here

score 0 · Answer 3 · answered Sep 17 '19 at 19:23

Assuming you're fine with a PL/SQL solution (you could do it in a single update statement but unless it's performance critical, I'd find the loop easier to follow)

begin
  for src in (select t.task_id, e.name
                from (select t.*,
                             row_number() over (order by dbms_random.value) rnk
                        from task t) t
                     join
                     (select e.*,
                             row_number() over (order by dbms_random.value) rnk,
                             count(*) over () num_emps
                        from employee e) e
                     on( mod( t.rnk, e.num_emps ) = e.rnk - 1 ) )
  loop
    update task
       set name = src.name
      where task_id = src.task_id;
  end loop;
end;
/

Basically, this is randomly sorting both lists and then going down the list of tasks and assigning the next employee to that task. If the number of tasks isn't a multiple of the number of employees, some employees will get an extra task but no employee will have more than 1 more task than another.

Is it possible to update rows randomly with a group of set values?

3 Answers3

Create tables

Merge

Results