David T. Ashley wrote:
I have a table with two integer fields (call them p and q).
When I insert a record with a known p, I want to choose q to be one larger
than the largest q with that p.
What is the best and most efficient way to do this?
For example, let's say the table contains (p,q):
1,1
1,2
1,3
2,1
2,2
2,3
2.4
2,5
3,1
3,2
If I insert a new record with p=2, I would want to choose q to be 6. But if
I insert a record with p=3, I would want to choose q to be 3.
Is there any alternative to locking the table, querying for max q with the
desired p, then inserting?
Yes
CREATE TABLE `t` (
`p` int(10) unsigned NOT NULL default '0',
`q` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`p`,`q`)
) ;
--
Chris W
KE5GIX
"Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm";
Ham Radio Repeater Database.
http://hrrdb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
