2

Does anyone know if it is possible to get the auto increment id of a field in a mysql table and use it in the same insert?

e.g

assuming the new id here would be 2, an evaluated statement would look as follows

"insert into table (field1) values( 'random-2')"

I know it's possible for me to return this in the code and run another insert, but I wondered if there was a quicker way to 'compute' this during the insert?

1 thought I had was "insert into table (field1) values( 'random' + (select max(id) FROM table) + 1)"

but I'm worried about possible issues with multiple inserts occurring at the same time.

Thanks

TommyBs
  • 9,354
  • 4
  • 34
  • 65
  • It's not possible and this has been asked many times before. Look to the right of this question and follow some links. – RichardTheKiwi Apr 04 '11 at 20:08
  • possible duplicate of [Use current auto_increment value as basis for another field in the same INSERT query - is this possible in MySQL?](http://stackoverflow.com/questions/767161/use-current-auto-increment-value-as-basis-for-another-field-in-the-same-insert-qu) – RichardTheKiwi Apr 04 '11 at 20:09
  • possible duplicate of [mysql Getting the same auto_increment value into another](http://stackoverflow.com/questions/4297462/mysql-getting-the-same-auto-increment-value-into-another) – Jeff Ferland Apr 04 '11 at 20:13
  • believe it or not I did search but couldn't find what I was looking for :S Still getting used to using SO though so apologies for the duplicate. – TommyBs Apr 04 '11 at 20:19

2 Answers2

2

It's not possible. You're gonna have to update the entry afterwards.

Erik B
  • 40,889
  • 25
  • 119
  • 135
1

I'm worried about possible issues with multiple inserts occurring at the same time

That's only part of the problem - insert ids are only suposed to be unique - not contiguous.

If you want to do it in a single call from the application then use a stored procedure to encapsulate the insert+update or use a trigger to fire an update on insert. Or use a sequence generator instead of an autoincrement.

symcbean
  • 47,736
  • 6
  • 59
  • 94