bahaa Posted September 24, 2011 Share Posted September 24, 2011 Hello, 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. Regards, Link to comment Share on other sites More sharing options...
thara Posted September 24, 2011 Share Posted September 24, 2011 initialize that field to a value and then increment it by a value as you need when you insert next row to the database... Link to comment Share on other sites More sharing options...
bahaa Posted September 24, 2011 Author Share Posted September 24, 2011 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 More sharing options...
thara Posted September 25, 2011 Share Posted September 25, 2011 mysqli_stmt_insert_id () function can be use to retrieve last inserted id. Link to comment Share on other sites More sharing options...
bahaa Posted September 25, 2011 Author Share Posted September 25, 2011 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 More sharing options...
bahaa Posted September 26, 2011 Author Share Posted September 26, 2011 I figured it out INSERT INTO wine(wineID, wineYear, wineName, winePrice, wineHarvest) SELECT 1+ (SELECT MAX(wineID) FROM wine), 2009, 'Vidal icewine', 13.5, '2010-01-29'; Link to comment Share on other sites More sharing options...
HartleySan Posted September 26, 2011 Share Posted September 26, 2011 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. Link to comment Share on other sites More sharing options...
bahaa Posted September 26, 2011 Author Share Posted September 26, 2011 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 More sharing options...
HartleySan Posted September 26, 2011 Share Posted September 26, 2011 Seems like a ridiculous example, without insulting your teacher too much. Your teacher should have at least come up with a practical usage, as opposed to forcing you to do something that you would never do. Link to comment Share on other sites More sharing options...
Recommended Posts