a -> b is one to many and b -> c is one to many so b_seq.currval would not
be the correct value for all instances of c
--
Jim Poe ( jpoe_at_fulcrumit.com )
<gmei_at_my-deja.com> wrote in message news:8o4pqm$bef$1_at_nnrp1.deja.com...
> 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 Fri Aug 25 2000 - 10:39:50 CDT
