From: Stut (stuttlegmail.com)
Date: Sun Nov 25 2007 - 07:50:21 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]