INSERT query using ID [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Access Auto-Increment Value During INSERT INTO Statement

I want to run an INSERT query on a table like for example with 2 fields: id, name.

However I want to use the id in the name field, such as to prepend the id to the name. Now I could INSERT and then use PHP to to get the last id and then UPDATE the name based on that. However I would like to avoid using more than 1 query for this. I could use the auto increment value but that is not safe since another query can开发者_如何学运维 be run in between getting the ID and running the INSERT. So is there safe way to use the id in a field?

---

There is no way to do it in a single query. However you can safely use UPDATE with LAST_INSERT_ID() because LAST_INSERT_ID() is safe. It will always return last ID inserted in current session (even if there are other sessions doing inserts at the same time)

Read more here: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

---

You could use an update query like this one to make sure you get the correct id

update myTable set name = concat(id, name) where <where condition>

if you want to add a space between the id and name simply use concat(id, ' ', name).

And if you really really want to do it in just one query I reckon it would be something like this:

insert into myTable values(null, concat((select auto_increment from 
information_schema.tables where table_schema = myDatabaseSchema and
table_name = myTable), <name data>));

EDIT: Ignore this max(id) thing - as pointed out in comments.

insert into myTable values(null, concat((select max(id)+1 
from myTable), <name data>));

However, you should avoid duplicating data like that, and simply concat it when selecting.