Update multiple rows of one table using modulas of another table

Question

create table queue(queue_id int primary key auto_increment, tele bigint);
insert into queue values
  (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)
, (null, null)

create table container (container_id int primary key auto_increment, queue_container_id int, source bigint);
insert into container values
  (null, 1, 1000000001)
, (null, 1, 1000000002)
, (null, 1, 1000000003)
, (null, 1, 1000000004)
, (null, 1, 1000000005)
, (null, 2, 1000000003)

My goal: I want to update the tele column with the appropiate source #.

I want to distribute the source #s evenly to the queue. One restraint, it must only use the given queue_container_id. For example, if I gave the queue_container_id of 1 it would only use those source numbers. The queue should have all tele numbers filled. Repeats should attempt to be evenly distributed first.

The hard way would be to get all the queue_ids and update each row at a time. I'm wondering if this is possible with one query, as this could be as many as 1 million rows to update at one time.

Answer 1

One option uses window functions and arithmetics. Assuming that queue_id always increment without gaps:

select *
from queue q
inner join (
    select c.*, 
        row_number() over(order by container_id) rn,
        count(*) over() cnt
    from container c
    where queue_container_id = 1
) c on (c.rn - 1) = (q.queue_id - 1) % c.cnt

This distributes evenly the containers of a given queue_container_id to the queue.

If queue_id is not reliable, we can generate our own sequence with row_number():

select *
from (
    select q.*, 
        row_number() over(order by queue_id) rn 
    from queue 
) q
inner join (
    select c.*, 
        row_number() over(order by container_id) rn,
        count(*) over() cnt
    from container c
    where queue_container_id = 1
) c on (c.rn - 1) = (q.queue_id - 1) % c.cnt

---

In earlier versions, options are limited. Emulating row_number() and the window count with subqueries will not scale well if you have a large number of rows. One alternative is user variables:

select *
from queue q
inner join (
    select c.*, @rn := @rn + 1 rn
    from (
        select * 
        from container c 
        where queue_container_id = 1 
        order by container_id
    ) c
    cross join (select @rn := 0) x      
) c on (c.rn - 1) = (q.queue_id - 1) % @rn

User variables are tricky to use, and are (finally!) planned for deprecation in future versions of MySQL. While the above solution works in MySQL 5.x, I would warmly recommend upgrading to MySQL 8.0, and use the window functions solution. This is the right way to do it.

Demo on DB Fiddle