Re: SQL Insert with Value & MAX?

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)?
Try something like this
insert into table_a (A,B)
select X, nvl(max(B),0)+1
from table_a
where A=X
group by X

--
**************************************************************
*                          Saad Ahmad                        *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************