0

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());
Barmar
  • 741,623
  • 53
  • 500
  • 612
gunnersboy
  • 25
  • 2

1 Answers1

1

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;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ye that was my fear. Can this be a performance hit? Say the table grows to like a million rows, will the update be problematic? – gunnersboy Aug 02 '23 at 22:36
  • No. The primary key is indexed, so searching for it is not dependent on the table size. – Barmar Aug 02 '23 at 22:43