David T. Ashley wrote:
> On 11/25/07, Stut <stuttle@stripped> 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
>>> 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):
>>> 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
>>> 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.
> 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.
You could probably do something using the if function. Untested but
insert into test1 set p = 4, q = if((select count(1) from test1 as tmp1
where p = 4) > 0, (select max(q) + 1 from test1 as tmp where p = 4), 1)