[SOLVED] MySQL - Get ID value (PK) during the INSERT, not after

MySQL - Get ID value (PK) during the INSERT, not after

I am trying to obtain the value of an auto increment ID during an insert and save that value in another field in the same table during the insert. I know of LAST_INSERT_ID() but I only get that value after the record has been entered. I am looking for a way to get the ID value during the execution of the insert and save it in another column.

My Example is:

CREATE TABLE `item` (
  `baseitemid` bigint NOT NULL AUTO_INCREMENT,
  `itemid` bigint DEFAULT NULL
  PRIMARY KEY (`baseitemid`),
  KEY `baseitem_itemid_idx` (`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

So I would like to get the value of baseitemid and store that same value in itemid. We have a use case where certain data the two values will have to match.

So when we run:

INSERT INTO item
(itemid)
VALUES(LAST_INSERT_ID());

We get a value of 0 in itemid. When I run the Insert again, I get the baseitemid value of the previous run. I need it during the run. Is this possible?

INSERT INTO item
(itemid)
VALUES(LAST_INSERT_ID());

Solution

I don't think there's a way to access it in the same INSERT. So you should split it into INSERT and UPDATE. You can use a transaction to make this atomic.

BEGIN TRANSACTION;
INSERT INTO items (itemid) VALUES (NULL);
UPDATE items SET itemid = LAST_INSERT_ID() WHERE baseitemid = LAST_INSERT_ID();
COMMIT;