MySQL - Insert multiple rows based on column value

Question

I have the query working, just wondering if there is a better way to do this without cursors/loops/php side. I've been a DBA for 5+ years and just came across the := statement. Very cool.

Table (tblPeople) with the person ID and the number of tickets they bought.

PersonId  NumTickets
1         3  
2         1 
3         1

I then want to assign individual tickets to each person in a new table (tblTickets), depending on how many tickets they bought. The TicketId is a key, auto increment column.

TicketId  PersonId
100       1
101       1
102       1 
103       2
104       3 

Here is the code. It loops through the whole tblPeople over and over again incrementing a new calculated column called rowID. Then I filter out the rows based on the number of tickets they bought in the WHERE clause. The problem I see is the subquery is huge, the more people I have, the bigger the subquery gets. Just not sure if there is a better way to write this.

INSERT INTO tblTickets (PersonId)
    SELECT PersonId
    FROM (
        SELECT s.PersonId, s.NumTickets,
            @rowID := IF(@lastPersonId = s.PersonId and @lastNumTickets = s.NumTickets, @rowID + 1, 0) AS rowID,
            @lastPersonId := s.PersonId,
            @lastNumTickets := s.NumTickets
        FROM tblPeople m,
            (SELECT @rowID := 0, @lastPersonId := 0, @lastNumTickets := 0) t
            INNER JOIN tblPeople s 
        ) tbl
    WHERE rowID < NumTickets

Answer 1

I'd add a utility table Numbers which contains all the numbers from 1 up to the maximal number of tickets a person may buy. Then you can do something like this:

INSERT INTO tblTickets (PersonId)
SELECT s.PersonId
FROM tblPeople s, Numbers n
WHERE n.number <= s.NumTickets

Answer 2

Following Stored procedure will serve your purpose...

DELIMITER $$

USE <your database name> $$

DROP PROCEDURE IF EXISTS `update_ticket_value2`$$

CREATE PROCEDURE `update_ticket_value2`()
BEGIN
DECLARE index_value INT;
DECLARE loop_variable INT;  


SET @KeyValue = 100;
SET @LastPersonID = 0;
SET @TicketNum = 0;

SET @PersonIDToHandle = 0;

SELECT @PersonIDToHandle = PersonID, @TicketNum = NumTickets
FROM tblPeople
WHERE PersonId > @LastPersonID
ORDER BY PersonId
LIMIT 0,1;

WHILE @PersonIDToHandle IS NOT NULL
DO
    SET loop_variable = 0;

    WHILE(loop_variable < @TicketNum) DO
        INSERT INTO tblTickets(TicketId, PersonId) VALUES(@KeyValue + loop_variable, @PersonIDToHandle);
        SET loop_variable = loop_variable + 1;
    END WHILE;  

    SET @LastPersonID = @PersonIDToHandle;
    SET @PersonIDToHandle = NULL;
    SET @KeyValue = @KeyValue + @TicketNum;

    SELECT @PersonIDToHandle := PersonID, @TicketNum := NumTickets
    FROM tblPeople
    WHERE PersonId > @LastPersonID
    ORDER BY PersonId
    LIMIT 0,1;
END WHILE;

    END$$

DELIMITER ;

Call the procedure as:

CALL update_ticket_value2();

Hope it helps...