Sometimes, auto increment doesn't meet our expectation. we want to insert the next highest id than the latest one exist on table. However auto increment will always increment whether the data with that id exists or deleted. So, many unused ids could be there those are deleted after insertions. To achieve this, we have to identify the current maximum id in the table and then insert the next highest id. To achieve this we may have to execute two sql statement in normal sense. One is to identify the max id and then the insert query that will insert data.
This can be much simpler and can be done in a single sql query. The query will be as follows:
INSERT into tblname(id,name) Select (isnull(MAX(id),0)+1),'test' from tblname
/*Edited the query to test whether id is null or not(set result to 1 if so) after getting feedback from one visitor*/
It will execute much faster than the individual sql queries. This is actuall a modified version of a select and insert type queries where data are selected from a table and inserted to another table.
2 comments:
Consider this example:
Crate a table:
create table testTable(id numeric(10), name varchar(15));
Now run the query:
insert into testTable(id,name) Select (MAX(id)+1),'test' from testTable;
If the table was initially empty then result is:
mysql> select *from testTable;
+------+------+
| id | name |
+------+------+
| NULL | test |
+------+------+
1 row in set (0.00 sec)
Actually this doesnt work if initially the table is empty.
hi, thanks for showing me the bug. However, we can overcome this very simply:
INSERT into tblTest(id,name) Select (isnull(MAX(id),0)+1),'test' from tblTest
Hope this one will help you :)
Post a Comment