title: MySQL insert from a table assigning auto-increment ID and update FK in third table
body: <p>I have 3 tables</p>
  
  <pre><code>old_customers
  id   name
   5   Mario
  13   John
  ..   ...
  
  new_customers
  id   name   address
   7   Luigi  Roma
  ..   ...    ...
  
  orders
  id   customer_id
   1   5
   2   7
   3   13
  ..   ..
  </code></pre>
  
  <p>I want to copy <code>old_customers</code> to <code>new_customers</code> assigning them a new auto-increment id and updating the orders foreign key <code>customer_id</code>.<br>
  How to perform this simultaneous INSERT and UPDATE in one simple MySQL query?  </p>
  
  <p>A basic psudo-sql idea</p>
  
  <pre><code>INSERT INTO new_customers (name) SELECT name FROM old_customers
  AND
  UPDATE orders SET customer_id=LAST_INSERT_ID() WHERE customer_id=old_customers.id
  </code></pre>
  
  <p><br><hr>
  A week later ...</p>
  
  <p>Thanks to help received this is the developed MySQL solution:<br>
  create a PROCEDURE that declare a CURSOR and INSERT+UPDATE fetched results in a LOOP</p>
  
  <pre><code>DELIMITER //
  
  CREATE PROCEDURE move_costumers()
  BEGIN
    DECLARE fetched_id INT(3);
    DECLARE fetched_name VARCHAR(50);
    DECLARE my_cursor CURSOR FOR SELECT id,name FROM old_customers;
  
    OPEN my_cursor;  
    BEGIN
      DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; 
      LOOP
        FETCH my_cursor INTO fetched_id,fetched_name; 
        INSERT INTO new_customers (name) VALUES (fetched_name);
        UPDATE orders SET orders.customer_id = LAST_INSERT_ID() 
          WHERE orders.customer_id = fetched_id;
      END LOOP;
    END;
    CLOSE my_cursor;
  END//
  </code></pre>
  
  <p>It's a loop without control variable and without label, as I found in <a href="http://forge.mysql.com/tools/tool.php?id=186" rel="nofollow">Simple Cursor Traversal 2</a></p>