MySQL Loop within a Loop to Transpose records

Question

I have a delivery table with the fields (id, quantity, productId, customerId, deliveryDate). There is a requirement to place a serialized tag on every product and maintain a log of the tag, product, customer and delivery date. The tagLog table is auto-incremented and the id represents the tag serial number

Delivery Table
id    quantity   productId     customerId     deliveryDate 
2085     4        10197          245          2020-06-05
2085     2        10433          245          2020-06-05

I want to loop through the delivery table (where not already tagged) and for each row, create an individual record in tagLog for the number in quantity field. For example, these two Delivery records should create 6 records in the tagLog table.

tagLog
tagId      productId     customerId    deliveryDate
20890        10197           245        2020-06-05
20891        10197           245        2020-06-05
20892        10197           245        2020-06-05
20893        10197           245        2020-06-05
20894        10433           245        2020-06-05
20895        10433           245        2020-06-05

Any advice on the construct of the inner loop would be appreciated.

Answer 1

SQL is a set-based language, that is not efficient at handling loops.

If you are running MySQL 8.0, you can do this with a recursive query (this is still an iterative process, but with better performance than a loop in a stored procedure):

with recursive cte as (
    select id, productId, customerId, deliveryDate, quantity 
    from delivery
    union all 
    select id, productId, customerId, deliveryDate, quantity - 1
    from cte
    where quantity > 0
)
select 
    row_number() over(order by id) tagId,
    productId,
    customerId,
    deliveryDate 
from cte

It is unclear which method you want to use to generate the tagId. This gives you an always-incrementing number that starts at 1, and where records of the same original id are consecutive.