Last Insert Question

Jen76 · #1 (**permalink**) 02-24-04, 01:27

I know that with Last_Insert_ID I can get the last inserted ID...but if the insert inserted more than one row, it only pulls the first id inserted within that group. How can I get all of the ID's inserted in that group?

aus · #2 (**permalink**) 02-24-04, 14:31

Do you always know how many rows you insert?

Mastulator · #3 (**permalink**) 02-24-04, 14:51

Well, assuming you can guarantee that nobody will write to the table at the same time that you are, you could always count the number of elements you're inserting, fetch the ID of the first one that was inserted, and then simply correlate them.

No idea on whether MySQL has transactions or any sort of normal locking though (I'm a PGSQL guy).

aus · #4 (**permalink**) 02-24-04, 15:08

Actually, you could do things this way:

Code:

LOCK TABLES inserttable WRITE;
INSERT INTO inserttable... <your insert statement>;
SELECT autoid FROM inserttable
WHERE autoid >= LAST_INSERT_ID();
UNLOCK TABLES;

That will give you all the ids that were inserted. I know that MyISAM tables do not use ids missing from the middle of the table, so you are safe there. The LOCK TABLES statement will guarantee that nobody interferes with your insert.

Jen76 · #5 (**permalink**) 02-27-04, 09:52

Thanks aus!! Works perfect!

Internet Services

Web Development

Digital Marketing

Consumer Tech