Re: SQL Insert with Value & MAX?
From: John Blackburn <jb2_at_qdot.qld.gov.au>
Date: 9 Mar 1995 03:09:21 GMT
Message-ID: <3jlrh1$1gn_at_camelot.qdot.qld.gov.au>
> > Given a table with columns A and B, the combination {A,B} being the
> > primary key. Given rows of {X,1}, {X,2}, and {X,3}, I'd like to INSERT
> > the row {X,<Max(B)+1}. My question: is this possible in a single SQL
> > statement, or must the MAX(B) WHERE A=X be saved off in a variable first,
> > and INSERTed into the table (plus one)?
Date: 9 Mar 1995 03:09:21 GMT
Message-ID: <3jlrh1$1gn_at_camelot.qdot.qld.gov.au>
> William Pollack (TJFD63A_at_prodigy.com) wrote:
> > Given a table with columns A and B, the combination {A,B} being the
> > primary key. Given rows of {X,1}, {X,2}, and {X,3}, I'd like to INSERT
> > the row {X,<Max(B)+1}. My question: is this possible in a single SQL
> > statement, or must the MAX(B) WHERE A=X be saved off in a variable first,
> > and INSERTed into the table (plus one)?
You could have a sequence which you only use when inserting into this table which is initialised to the maximum value currently in the table (or wherever you want to start the numbers at. :-)
INSERT into TABLE
select X,PK_SEQ.NEXT_VALUE
from wherever;
This assumes that you only ever insert into the table using the sequence to give you the correct value. But then if you're developing the application, you should have control over that.
-- John Blackburn Phone: +61 7 2534634 jb2_at_qdot.qld.gov.au Fax: +61 7 8541194Received on Thu Mar 09 1995 - 04:09:21 CET