MySQL Cursor Loop does not update

Question

I would be grateful for some help with a MySQL Stored Procedure that I have written. It is running on MySQL 5.6.11 on Windows and I have read and re-read the MySQL docs and carried out a number of google searches.

Background

I have database for storing information associated with products and in some cases I want to create new associated information by copying from one product record and inserting for another product record or from one product record and inserting to all product records in a specific product group. I can successfully copy from one one product record and insert for another product record but I am having trouble copying from one product record then inserting for all product records in a specific product group.

Initial research suggest that a cursor as part of a stored procedure may provde a solution. In terms of the problem I have there are two tables involved, one bullets, this holds bullet points and I want to copy them. The second table is the master table and it holds products data such as product code and the product group code associated with the product code.

I wish to create new records in the bullets table by:

• Retrieving the bullet records for a specific product code, for example I have 4 bullet reocrds for product code 10001.
• Then I wish to select all products for product group, for example there are 81 product codes in product group 3.
• Then for each product code in product group 3 I want to create one new record per bullet.

This would mean that 81 x 4 new records would be added to the bulets table.

Note I am using LibreOffice Base, forms and Macros to ineract with MySQL.

What I have done

I have worked out I can do a one to one copy using the following statement:

INSERT INTO bullets (product_code, bullet_text) SELECT 'fromCode', bullet_text
FROM bullets WHERE product_code = 'toCode'

Where the fromCode and toCode are provided by a LibreOffice Base form and processed using a LibreOffice BASIC script and this all works OK. Hope I am not providing too much superfluous information.

So my thought was that I could write a stored procedure using a cursor to get the product codes for a specific product group, then loop through the product codes to write my new records. Below is the stored procedure:

BEGIN
    DECLARE product_code_from_group VARCHAR(30);
    DECLARE from_product_code VARCHAR(30);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_get_product_codes CURSOR FOR
        SELECT product_code FROM master
        WHERE product_group = to_product_group ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

    OPEN cur_get_product_codes;

    copybullets: LOOP
        FETCH cur_get_product_codes INTO product_code_from_group;
    IF done THEN
        CLOSE cur_get_product_codes;
        LEAVE copybullets;
    END IF;

    INSERT INTO bullets (product_code, bullet_text) 
    SELECT product_code_from_group, bullet_text 
    FROM bullets
    WHERE product_code = from_product_code;

    END LOOP;

    CLOSE cur_get_product_codes;

END

When I execute it appears to run but no new records are created. I am testing it using

 CALL copy_bullets_test(10001,3)

• 10001 is the code I am getting the bullets from
• 3 is the product group I am using the get a list of procducts.

I am pretty sure the problem is my limited knowledge, I have carefully read a replicated the code on Roland Boumans blog but I must be missing something. It looks as if my code is not iterating though the loop but I didn't know why.

Answer 1

If I understand correctly all you need is CROSS JOIN. Try

INSERT INTO bullets (product_code, bullet_text) 
SELECT m.product_code, b.bullet_text 
  FROM bullets b CROSS JOIN master m
 WHERE b.product_code = 10001
   AND m.product_group = 3
   AND m.product_code <> 10001;

Here is SQLFiddle demo.

Now you can wrap it up into a stored procedure if you went to

CREATE PROCEDURE copy_bullets_test (IN product_code_from INT, IN product_group_to INT)
INSERT INTO bullets (product_code, bullet_text) 
SELECT m.product_code, b.bullet_text 
  FROM bullets b CROSS JOIN master m
 WHERE b.product_code = product_code_from
   AND m.product_group = product_group_to
   AND m.product_code <> product_code_from;

And use it

CALL copy_bullets_test(10001, 3);

Here is SQLFiddle demo for that case.