Re: HowTo: Select from insert-table WHILE inserting

try this. Take the greatest of the old one and new one.

insert into tablea (age,name,highestyet) select tp.age,tp.name,greatest(tt.maxage,tt2.maxage2) from tablepersons tp, (select max(age) maxage from tablea) tt,(select max(age) maxage2 from tablepersons) tt2;;

Neal

Andreas Koch wrote:

> Hi,
>
> what is the best way to get something like
>
> insert into tablea (age,name,highestyet)
> select age,name,(select max(age) from tablea) from tablepersons;
>
> or, for versions below 8i,
>
> insert into tablea (age,name,highestyet)
> select tp.age,tp.name,tt.maxage
> from tablepersons tp, (select max(age) maxage from tablea) tt;
>
> to work correctly.
>
> These two scripts work, but maxage returns the highest age in
> tablea BEFORE the insert - i need the highest age WHILE
> the insert.
>
> Any ideas?
> The solution has to work without redefining the tables and
> from an SQL script batch run.
>
> --
> Andreas
> Who doesn't live can never die
Received on Thu Sep 27 2001 - 14:09:32 CDT