Jump to content
Larry Ullman's Book Forums

How To Insert The Next Available Id If The Field Not Auto Increment?

Recommended Posts



How do I insert the next available id for a field if it is not auto increment?

Is there a function I can use within the insert statement to get the last id and increment it by one?


I was trying this

INSERT INTO wine(wineID, wineYear, wineName, winePrice, wineHarvest)

VALUES((select(MAX(wineID)+1) FROM wine ), 2009, 'Vidal icewine', 13.5, '2010-01-29');


But It did not work. It said, Error Code: 1093. You can't specify target table 'wine' for update in FROM clause


I know you would say why just don't you use auto increment on the id field :)

This table was given to us by the teacher in school and she did not auto increment the id field.



Link to comment
Share on other sites

initialize that field to a value and then increment it by a value as you need when you insert next row to the database...

The table she gave us contains 23 records, so I need to insert the record number 24, but I want to be able to insert id 24 without going back to the table and look what was the last record.

Is there a function to do that in the insert statement?

Link to comment
Share on other sites

mysqli_stmt_insert_id () function can be use to retrieve last inserted id.

I am not trying to retrieve the last id I inserted.

I want to insert a new record into a table and used the next available id for a field that is not auto increment.

All I am tasking is that if there is a function to be used within the insert statement to insert the next available id.

Link to comment
Share on other sites

So basically a query within a query? I suppose that would work, but I can't understand why you wouldn't just auto increment the ID field instead.

If I was working on this project, I would use the auto increment, but we are taking this in school and the teacher meant to not auto increment the ID field for teaching purposes , so she can show us how to use the sub query.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...