On 11/25/07, Stut <[EMAIL PROTECTED]> wrote: > 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? > > 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 > > -- > http://stut.net/
Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave.
