title: MySQL Cursor Loop does not update
body: <p>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.</p>
  
  <p><strong>Background</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I wish to create new records in the bullets table by:</p>
  
  <ul>
  <li>Retrieving the bullet records for a specific product code, for
  example I have 4 bullet reocrds for product code 10001.</li>
  <li>Then I wish to select all products for product group, for example there are 81
  product codes in product group 3.</li>
  <li>Then for each product code in product group 3 I want to create one new record per bullet.</li>
  </ul>
  
  <p>This would mean that 81 x 4 new records would be added to the bulets table.</p>
  
  <p>Note I am using LibreOffice Base, forms and Macros to ineract with MySQL.</p>
  
  <p><strong>What I have done</strong></p>
  
  <p>I have worked out I can do a one to one copy using the following statement:</p>
  
  <pre><code>INSERT INTO bullets (product_code, bullet_text) SELECT 'fromCode', bullet_text
  FROM bullets WHERE product_code = 'toCode'
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When I execute it appears to run but no new records are created.  I am testing it using</p>
  
  <pre><code> CALL copy_bullets_test(10001,3)
  </code></pre>
  
  <ul>
  <li>10001 is the code I am getting the bullets from</li>
  <li>3 is the product group I am using the get a list of procducts.</li>
  </ul>
  
  <p>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.</p>