Is it possible to INSERT and then SELECT the inserted row in one query?

[cronoklee]

Is it possible to insert a row and return it in the one statement? It
may sound strange to request values you've just inserted, but things
like now(), default column values and obviously auto update IDs are
calculated by the database during the insert.

Cheers,
Ciarán

[Gordon Burditt]

Post by cronoklee
Is it possible to insert a row and return it in the one statement? It
may sound strange to request values you've just inserted, but things
like now(), default column values and obviously auto update IDs are
calculated by the database during the insert.

No.

For auto_increment columns, LAST_INSERT_ID() will give you the value
for the last-inserted row *on this connection* with an auto_increment
column. Actions on other connections do not affect it, so there's
no need for locking against that possibility. If the auto_increment
column is a primary key, you can then select it using only "WHERE
id = LAST_INSERT_ID()".

SELECT LAST_INSERT_ID();

can also be used to get the id for later use (e.g. inserting several
records that refer to this one, which may have auto_increment columns
of their own which would change LAST_INSERT_ID().

[cronoklee]

Post by Gordon Burditt

Post by cronoklee
Is it possible to insert a row and return it in the one statement? It
may sound strange to request values you've just inserted, but things
like now(), default column values and obviously auto update IDs are
calculated by the database during the insert.

No.
For auto_increment columns, LAST_INSERT_ID() will give you the value
for the last-inserted row *on this connection* with an auto_increment
column.  Actions on other connections do not affect it, so there's
no need for locking against that possibility.  If the auto_increment
column is a primary key, you can then select it using only "WHERE
id = LAST_INSERT_ID()".
SELECT LAST_INSERT_ID();
can also be used to get the id for later use (e.g. inserting several
records that refer to this one, which may have auto_increment columns
of their own which would change LAST_INSERT_ID().

OK, thanks! Seems like it might be a good addition for next syntax
update.

Cheers,
Ciarán