Michael Bishop Michael Bishop - 5 months ago 43
SQL Question

MySQL Cursor Loop does not update

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

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.

Comments