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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...