mysql> INSERT the largest +1

[midnite]

excuse me, how to insert one row right after the largest one?for example:

+----+| id |+----+|  1 ||  2 ||  3 ||  4 |+----+mysql> INSERT INTO acc_list VALUES ((SELECT max(id) FROM acc_list)+1);+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+

this is a fake one of course. The SQL is incorrect! But it at least showing the concept and let you understand my problem. It can be done by two queries. But making it in one single query can prevent some errors and speed up performance.It can also be done by AUTO_INCREMENT. But in my case, using AUTO_INCREMENT will not be that handy.

[aspnetguy]

The following query was done in SQL Server. The sub query is correct however it still throws and error saying sub queries are not allowed here. I assume MySql would do the same.INSERT INTO Region VALUES((SELECT TOP 1 RegionID+1 FROM Region ORDER BY RegionID DESC),'North East')I think your only options are, if you use MySql 5.x, you can use the new equivalent to TSQL or you will have to run a seperate subquery on the server side code to get the next ID value before you tryt he insert.

[Yahweh]

Use this:

INSERT INTO	acc_list	(ID)SELECT   Max(b.ID + 1)FROM   acc_list b

Notice that the query above does not have a "VALUES (...)" clause. It doesn't need one because its using a SELECT.

[midnite]

aspnetguy:MySQL still complaining that the same table cannot appear in both INSERT and the FROM clause..it seems that MySQL and Access are using something quite different.Yahweh:Oh thanks!! It works fine.But how to alter the SQL if my table got more than one column? The one i raised is just an example and make things simpler. The real thing of course having many columns, a single one can do nothing.

[jesh]

Typically, you would set your ID column to be auto-incrementing.If your table were set up like this:

id   username1	midnite2	aspnetguy3	Yahweh

You'd INSERT a new record like this:

INSERT INTO myTable (username) VALUES ('jesh');

And the table would look like this because of the auto-incrementing id column:

id   username1	midnite2	aspnetguy3	Yahweh4	jesh

[Yahweh]

aspnetguy:MySQL still complaining that the same table cannot appear in both INSERT and the FROM clause..it seems that MySQL and Access are using something quite different.Yahweh:Oh thanks!! It works fine.But how to alter the SQL if my table got more than one column? The one i raised is just an example and make things simpler. The real thing of course having many columns, a single one can do nothing.

I can show you the code, but if you want to understand it, then you have to understand that literally, the "VALUES (...)" clause is a recordset, fundamentally no different from the recordset returned by any SQL query. That might not mean much to you, until you realize that the following two queries are identical:

INSERT INTO	table	(field1)VALUES	('monkey');INSERT INTO	table	(field1)SELECT	'monkey';   -- the query "SELECT 'monkey'" returns a single record with a single field containing the value 'monkey'

As you can probably guess, its not that hard to alter many columns, its not different than writing a select statement with many columns. The following two queries are identical:

INSERT INTO	table1	(field1, field2, field3, field4)VALUES	('1', 'monkey', 'jesh', 'aspnetguy')INSERT INTO	table1	(field1, field2, field3, field4)SELECT	field1, field2, field3, field4FROM	sometableWHERE	[conditions]

For your application, you might use this code:

INSERT INTO	acc_list	(ID, article_title, date_added)SELECT   Max(b.ID + 1), 'Article on monkeys', Now()FROM   acc_list b

Its very simple, and very intuitive. Just be aware of two things:1) Your select statement should contain exactly the number fields that you are updating.2) You will insert exactly the number of rows returned by your select statement. If your select statement returns 12 rows, you will insert 12 new rows into your table.

[jesh]

It can also be done by AUTO_INCREMENT. But in my case, using AUTO_INCREMENT will not be that handy.

Sorry, I skipped over that part of your original post. Heh.

[midnite]

first of all, really a BIG THANKS to Yahweh!! You really genius!!aspnetguy and jesh, also thanks very much for your help.in fact, what i want to do is to keep the list tidy. Let's say there are 5 rows and originally their id are 1 to 5. If id=3 have left, the list will become 1,2,4,5. So i would like to make it to 1,2,3,4. Hope you understand :]For instant, i have think of two approaches.1) using AUTO_INCREMENTwhenever a row is deleted, change the largest id to the one deleted if the id of the row being deleted is less than the largest id.(exceptional case occurs when the largest row is the one being deleted. 1,2,3,4,5 -> 1,2,3,4 -> 1,2,3,5)then set AUTO_INCREMENT to the id being deleted.(in the first case, 1,2,3,4,5 -> 1,2,4,5 -> 1,2,3,4 and set AUTO_INCREMENT = 3. The AUTO_INCREMENT will be 5 but not 3)2) not using AUTO_INCREMENTwhenever a row is deleted, fill it in with the largest. It is just the same here with method 1.when a row need to be insert, find the maximum and insert it at the max+1. That is what i was asking.method 1 won't work in some of my tables because they have more than one column require this kind of operation. MySQL doesn't allow me to specify more than one AUTO_INCREMENT column.also for performance considering, setting AUTO_INCREMENT to a low value, then MySQL automatically enlarge it to the appropriate value. This may be a slow process. Also whenever we set the AUTO_INCREMENT, we will notice that ALL the rows of the table are affected!!

mysql> ALTER TABLE acc_list AUTO_INCREMENT = 5;Query OK, 10 rows affected (0.09 sec)Records: 10  Duplicates: 0  Warnings: 0

so, the second method will probably be better. Am i right?