Auto increment two fields simultaneously?

[bkeep]

In my db table I have these fields id,sort_order,info,etc

The id field is setup as an autoincrement primary key the sort_order field is setup as just an int(5)

id sort_order info
1 1 test
2 2 test
3 4 test
4 3 etc

When a new item is added is there a way to autoincrement the sort_order field at the same time as the id field or should I just use two queries and set the sort_order with last insert id?

Best Regards,
Brandon

[Pico_Train]

Yeah, that's what I was going to say, get the last inserted ID and then update the sort order and set it the same as the id.

[Readie]

Have you tried saying:

INSERT INTO
  table_name
SET
  a = "val a",
  b = "val b",
  sort_order = id

Think that should work.

[penders]

Just to clarify...

12.1.10. CREATE TABLE Syntax [dev.mysql.com]

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.
:
For MyISAM and BDB tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, Using AUTO_INCREMENT.

But having an "AUTO_INCREMENT secondary column in a multiple-column key" is not what is required in this instance.

[penders]

...or should I just use two queries and set the sort_order with last insert id?

Just wondering... it kind of feels you should be able to do something like this in 1 query?