Welcome to WebmasterWorld Guest from 23.20.230.24

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Auto increment two fields simultaneously?

     
3:07 pm on Jul 23, 2011 (gmt 0)

5+ Year Member



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
8:03 am on Jul 24, 2011 (gmt 0)

10+ Year Member



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.
9:17 pm on Jul 26, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Have you tried saying:


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


Think that should work.
1:54 pm on Aug 2, 2011 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
4:05 pm on Aug 2, 2011 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



...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?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month