>From http://www.mysql.com/doc/en/Miscellaneous_functions.html:
LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into an
AUTO_INCREMENT column. See section 8.4.3.126 mysql_insert_id().
mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0). If you
insert many rows at the same time with an insert statement,
LAST_INSERT_ID() returns the value for the first inserted row. The reason
for this is to make it possible to easily reproduce the same INSERT
statement against some other server. If expr is given as an argument to
LAST_INSERT_ID(), then the value of the argument is returned by the
function, and is set as the next value to be returned by LAST_INSERT_ID().
This can be used to simulate sequences: First create the table:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Then the table can be used to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
You can generate sequences without calling LAST_INSERT_ID(), but the
utility of using the function this way is that the ID value is maintained
in the server as the last automatically generated value (multi-user safe).
You can retrieve the new ID as you would read any normal AUTO_INCREMENT
value in MySQL. For example, LAST_INSERT_ID() (without an argument) will
return the new ID. The C API function mysql_insert_id() can also be used to
get the value. Note that as mysql_insert_id() is only updated after INSERT
and UPDATE statements, so you can't use the C API function to retrieve the
value for LAST_INSERT_ID(expr) after executing other SQL statements like
SELECT or SET.
--
Ed Carp, N7EKG http://www.pobox.com/~erc
214/986-5870
Licensed Texas Peace Officer
Computer Crime Investigation Consultant
Director, Software Development
Escapade Server-Side Scripting Engine Development Team
Pensacola - Dallas - London - Dresden
http://www.squishedmosquito.com
"The whole aim of practical politics is to keep the populace alarmed-- and
thus clamorous to be led to safety-- by menacing it with an endless series
of hobgoblins, all of them imaginary."
-- H. L. Mencken
> -----Original Message-----
> From: Romans Stepanovs [mailto:roman16@;btv.lv]
> Sent: Wednesday, October 23, 2002 6:47 PM
> To: [EMAIL PROTECTED]
> Subject: Question about AUTO_INCREMENT
>
>
> Hello,
> I'm a newbie in MySQL and also in this mailing list.
> I've a problem and I hope some gurus can help me.
> So the question is: How to obtain an index of the row which is inserted ?
> for example :
> INSERT INTO some_tbl (ID,field1) VALUES ('','blahblahblah');
> I need 'ID' to use as reference value in different rows in another table.
> Is it possible to get 'ID' in the same query or I need to do
> something else?
>
> Thanks,
> Roman
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
