List:General Discussion« Previous MessageNext Message »
From:Stut Date:November 25 2007 6:07pm
Subject:Re: Incrementing a "Private" Integer Space
View as plain text  
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/
Thread
Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
  • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
  • Re: Incrementing a "Private" Integer SpaceStut25 Nov
    • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
      • Re: Incrementing a "Private" Integer SpaceStut25 Nov
    • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
      • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
        • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
        • RE: Incrementing a "Private" Integer SpaceMartin Gainty25 Nov
          • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov