CODEWITHSUNDEEP
mysqlselectinsertauto-increment
Endeavor99
Endeavor99

Reputation: 75

Mysql: Use auto increment IDs from INSERT SELECT for subsequent INSERT

Short version

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.

INSERT ... SELECT creates multiple rows but you cannot obtain the auto-increment ID from them, for use in a subsequent INSERT statement.

Expanded version

I'm looking for an efficient way to use the auto increment IDs, created from an INSERT ... SELECT, in a second INSERT.

Imagine this scenario in a warehouse.

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.

The issue is linking the account reduction to the item dispatch. There are 3 tables:

GoodsIn: records the pallet arrival from the supplier

CREATE TABLE GoodsIn ('InID' 'CustomerID', 'ItemSKU_ID', 'HasBeenChecked')

GoodsOut: records the SKU dispatch to the Customer

CREATE TABLE GoodsOut ('OutID', 'CustomerID', 'ItemSKU_ID', 'DateDispatched')

Account: records each Customer transaction/balance

CREATE TABLE Ledger ('LedgerID', 'BalanceClose', 'AdjustmentAmount',  'CustomerID', 'ActionID')

(I've massively simplified this - please accept that GoodsIn and GoodsOut cannot be combined)

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

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)

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.

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.

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.

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.

But because I'm using an INSERT ... SELECT, I can't get the auto-increment ID of each row.

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.

It doesn't feel very elegant and safe though.

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.

Upvotes: 2

Views: 2585

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562951

You're right, when you do INSERT...SELECT for batch inserts, you don't have easy access to the auto-increment id. LAST_INSERT_ID() returns only the first id generated.

One documented behavior of bulk inserts is that the id's generated are guaranteed to be consecutive, because bulk inserts lock the table until the end of the statement.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html says:

  • innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

    This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.

This means if you know the first value generated, and the number of rows inserted (which you should be able to get from ROW_COUNT()), and the order of rows inserted, then you can reliably know all the id's generated.

The MySQL JDBC driver relies on this, for example. When you do a bulk insert, the full list of id's generated is not returned to the client (that is, the JDBC driver), but the driver has a Java method to return the full list. This is accomplished by Java code inferring the values, and assuming they are consecutive.

Upvotes: 3

Related Questions