Column Maximum +1

[Namadoor]

Hi,

I have an interger column in my database. In one of my SQL statments I want to peform a query that will insert a value into that interger column that is equal to the record with the greatest value plus 1. I know about auto_increment, but this does not do what I want it to do. This field is also not being used as a key, it is being used for ordering. Does anyone know how to do this in a single query, and possibly know a way that will work on any/most SQL languages, not just MySQL?

Thanks.

[toplay]

I don't know about other SQL servers, but MySQL can't do it in one query (with update). Quote from the manual:

http://dev.mysql.com/doc/mysql/en/update.html wrote:

Currently, you cannot update a table and select from the same table in a subquery.

To update in MySQL, do it in two SQL statements:

$select = 'SELECT MAX(column_name) + 1 AS next_order_nbr FROM table_name GROUP BY column_name';
// do query and fetch and assign it to $next_order_nbr

$update = "UPDATE table_name SET column_name = '$next_order_nbr' WHERE ...";
// do update query

For insert, you need MySQL version 4.0.14 or higher. Example:

$insert = 'INSERT INTO table_name (column_name) SELECT MAX(column_name)+1 FROM table_name GROUP BY column_name';
// Do insert query

http://dev.mysql.com/doc/mysql/en/insert-select.html

These should at least give you clues on how to do it with other SQL servers. Check the manual of that particular SQL server.

🙂