David T. Ashley wrote:
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.
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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
