Re: insert/select

In article <8o3q8i$a3i_at_dispatch.concentric.net>,   "Jim Poe" <jpoe_at_fulcrumit.com> wrote:
> I have three tables, A, B, and C. C is a detail of B and B is a detail of
> A.
>
> table A
> A_ID
> NAME
>
> table B
> B_ID
> A_ID
> FOO
>
> table C
> C_ID
> B_ID
> BAR
>
> I would like to copy a record in A and all it's details. I can copy A and
> the details from B using
>
> insert into A( A_ID, NAME )
> select A_SEQ.NEXTVAL, NAME
> from A where A_ID=OldA_ID
>
> select A_SEQ.CURRVAL into NewA_ID from dual;
>
> insert into B ( B_ID, A_ID, FOO )
> select B_SEQ.NEXTVAL, NewA_ID, FOO
> from B where A_ID=OldA_ID
>
> How do I copy B's details from C? Can I do this without a cursor and a
> loop?
>
> --
> Jim Poe ( jpoe_at_fulcrumit.com )
>
>

after your code, do:

select B_ID
into oldB_ID
from B
where A_ID = oldA_ID;

insert into C (C_ID, B_ID, BAR)
select C_SEQ.nextval, B_SEQ.CURRVAL, BAR from C
where B_ID = oldB_ID;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 24 2000 - 22:40:09 CDT