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
>> 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.
You could probably do something using the if function. Untested but
should work...
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)
-Stut
--
http://stut.net/
