On 11/25/07, Chris W <[EMAIL PROTECTED]> wrote: > > Stut wrote: > > > > insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp > > where p = 2) > > > > Probably not very efficient, but it works. > > > > -Stut > > > Auto increment is much easier to do. If your primary key is made up of > two fields and one of them is Auto Increment, then it will have the > desired behavior, just do this experiment and see.... > > CREATE TABLE `t` ( > `p` int(10) unsigned NOT NULL default '0', > `q` int(10) unsigned NOT NULL auto_increment, > PRIMARY KEY (`p`,`q`) > ) ; > > INSERT INTO `t` (`p`,`q`) VALUES > (1,NULL), > (1,NULL), > (1,NULL), > (2,NULL), > (2,NULL), > (2,NULL), > (2,NULL), > (2,NULL), > (3,NULL), > (3,NULL); > > -- > Chris W > KE5GIX
Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. <BEGIN IMPORTANT POINT> I don't suppose you know the section in the manual that defines the behavior you're describing? <END IMPORTANT POINT> The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in "q" would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave.
