Re: insert into one table with selects from 4 others

Thank you. Now here is a new wrinkle. I also need a column which is computed from v1and v2 in tablea. Not sure if it can be done at the same time.

insert into tablea (v1, v2, v3, v4)
select *
from (select sysdate v1 from dual),

(select avg(something) v2 from tableb),
(select min(something) v3 from tablec),
( select v2/v3 * 100 v4  from tablea);

This fails to insert even one row.

Please recommend a book filled with excellent SQL examples. I know it will get a lot of use here.

Chris O'Connor

In article <36ebace0.2509498_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to chrisoc_at_ans.net
> (if that email address didn't require changing)
> On Fri, 12 Mar 1999 21:35:27 GMT, you wrote:
>
> >
> >
> >I need some sample syntax doing this:
> >
>
> I think you want to insert/select. you seem mixing update and insert
> together... but anyway see if:
>
> insert into tablea ( v1, v2, v3, v4 )
> select *
> from ( select sysdate v1 from dual ),
> ( select sum(something) v2 from tableb ),
> ( select min(something) v3 from tablec ),
> ( select avg(something) v4 from tabled )
> /
>
> >Update TableA insert (V1, V2, V3 V4)
> >using (select sysdate from dual V1, select sum(something) from TableB V2,
> > select min(something) from TableC V3, select avg(something) from TableD V4)
> >
> >assume the data types are 100% compatible
> >
> >Thanks
> >I am stumbling around and cannot hit on the syntax to make SQLPlus happy.
> >
> >Chris O'Connor
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 16 1999 - 14:23:41 CST