Re: HowTo: Select from insert-table WHILE inserting

Andreas Koch skrev i meddelelsen
<9ot2j6$u3s$01$1_at_news.t-online.com>...
>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
>

Just do SET READ_CONSISTENT = 0 in your init.ora No, just joking - no such parameter :)
Write a PL/SQL loop that has a cursor running through tablespersons. For each row select the maxage from tablea and then insert that maxage with the data form the tablepersons cursor. If you dont have an order by on the select from tablepersons your maxage in tablea will be somewhat random.

/Peter Laursen Received on Wed Sep 26 2001 - 21:10:55 CDT