title: Mysql: Use auto increment IDs from INSERT SELECT for subsequent INSERT
body: <h2>Short version</h2>
  
  <p>Would someone provide an example of this? There are 3 SQL tables. Using INSERT ... SELECT, take data from table 1 and insert it into table 2. Then, INSERT rows into table 3, using the auto-increment id of each table 2 row just inserted using that INSERT ... SELECT statement.</p>
  
  <p>INSERT ... SELECT creates multiple rows but you cannot obtain the auto-increment ID from them, for use in a subsequent INSERT statement.</p>
  
  <h2>Expanded version</h2>
  
  <p>I'm looking for an efficient way to use the auto increment IDs, created from an INSERT ... SELECT, in a second INSERT.</p>
  
  <p>Imagine this scenario in a warehouse.</p>
  
  <p>The warehouse receives a pallet of goods from a supplier. The pallet contains multiple individual items, which must be dispatched to different customers. The pallet is booked in, broken down and checked. Each item is then assigned to the correct customer and marked as "ready". At this point, each item is dispatched with the dispatch status recorded per customer. Each Customer's account balance is reduced by a given value based on the item. </p>
  
  <p>The issue is linking the account reduction to the item dispatch. There are 3 tables:</p>
  
  <p>GoodsIn: records the pallet arrival from the supplier</p>
  
  <pre><code>CREATE TABLE GoodsIn ('InID' 'CustomerID', 'ItemSKU_ID', 'HasBeenChecked')
  </code></pre>
  
  <p>GoodsOut: records the SKU dispatch to the Customer</p>
  
  <pre><code>CREATE TABLE GoodsOut ('OutID', 'CustomerID', 'ItemSKU_ID', 'DateDispatched')
  </code></pre>
  
  <p>Account: records each Customer transaction/balance</p>
  
  <pre><code>CREATE TABLE Ledger ('LedgerID', 'BalanceClose', 'AdjustmentAmount',  'CustomerID', 'ActionID')
  </code></pre>
  
  <p>(I've massively simplified this - please accept that GoodsIn and GoodsOut cannot be combined)</p>
  
  <p>When an SKU is marked as ready for dispatch, I can use the following to automatically update the Ledger balance, taking the last balance row per customer and updating it</p>
  
  <pre><code>INSERT INTO Ledger (BalanceClose, AdjustmentAmount, CustomerID) 
  SELECT Ledger.BalanceClose + 
      (SELECT @Price:=ItemSKUData.ItemPrice FROM ItemSKUData WHERE ItemSKUData.ItemSKU_ID = GoodsIn.ItemSKU_ID)  AS NEWBALANCECLOSE,
      @Price AS ADJUSTMENTAMOUNT,
      Ledger.CustomerID
  FROM Ledger
  INNER JOIN GoodsIn ON GoodsIn.CustomerID = Ledger.CustomerID
  WHERE GoodsIn.HasBeenChecked = TRUE
      AND Ledger.LedgerID IN (SELECT MAX(Ledger.LedgerID) FROM Ledger GROUP BY Ledger.CustomerID)
  </code></pre>
  
  <p>This all works absolutely fine - I get a new Ledger row, with the updated BalanceClose, for each GoodsIn row where GoodsIn.HasBeenChecked = TRUE. Each of these Ledger rows gets an auto-increment Ledger.LedgerID on INSERT.</p>
  
  <p>I can then do pretty much the same code to INSERT into the GoodsOut table. Again as with Ledger, GoodsOut.OutID is an auto-increment ID.</p>
  
  <p>I now need to link those Ledger rows (Ledger.ActionID) to the GoodsOut.OutID. This is the purpose of Ledger.ActionID - it needs to map to each GoodsOut.OutID, so that the reduction of the Ledger balance is linked to the action of sending the goods to the customer.</p>
  
  <p>In theory, if this was a single INSERT and not an INSERT SELECT, I would simply take the GoodsOut.LAST_INSERT_ID() and use it on the INSERT INTO Ledger.</p>
  
  <p>But because I'm using an INSERT ... SELECT, I can't get the auto-increment ID of each row.</p>
  
  <p>The only way I can see to do this is to use a dummy column in the GoodsOut table, and store the GoodsIn.InID in it. I could then get the GoodsOut.OutID using a WHERE in the INSERT ... SELECT for the Ledger.</p>
  
  <p>It doesn't feel very elegant and safe though.</p>
  
  <p>So this is my question. I need to link table A to table B using table B's auto-increment ID, when all rows in BOTH table A and table B are created using INSERT ... SELECT.</p>