Re: Thanks for the help but...

Naresh Ramamurti wrote:
>
> Ken Kennedy wrote:
> >
> > ..now I have a more difficult problem. Since you proved yourself
> > on the last test, I thought I would up the anti.
> >
> > To refresh your memory...
> > Ken Kennedy wrote:
> > >
> > > Here is my dilema...
> > >
> > > I have two tables in production that appear as shown below:
> > >
> > > SQL> desc TABLE_A
> > > Name Null? Type
> > > ------------------------------- -------- ----
> > > IDENTIFIER_ID NOT NULL NUMBER
> > > SOME_FIELD_1 VARCHAR2(20)
> > > SOME_FIELD_2 NUMBER
> > >
> > > SQL> desc TABLE_B
> > > Name Null? Type
> > > ------------------------------- -------- ----
> > > IDENTIFIER_ID NOT NULL NUMBER
> > > FK_TABLE_A_ID NUMBER
> > > SOME_OTHER_FIELD VARCHAR2(20)
> > >
> > > Currently, only TABLE_A is populated and TABLE_B is
> > > a new table. I need to insert an entry in TABLE_B for each
> > > row in TABLE_A. If there are 30 rows in TABLE_A, I need
> > > to insert 30 rows in the new table. The IDENTIFIER_ID is a
> > > sequence number (SOME_SEQ) and the FK_TABLE_A_ID
> > > is a foreign key to TABLE_A.IDENTIFIER_ID.
> >
> > Your suggestion of:
> > insert into TABLE_B
> > select
> > SOME_SEQ.NEXTVAL,
> > TABLE_A.IDENTIFIER_ID,
> > 'SOME VALUE'
> > from
> > TABLE_A
> > ;
> > commit;
> >
> > worked perfectly.
> >
> > Now, lets say 10 entries already exist in TABLE_B with foreign
> > keys to their corresponding row in TABLE_A. Is there a way to
> > NOT insert a new row when a row when one alerady exists with a
> > foreign key to a row in TABLE_A? I'd like to see only 20 rows
> > inserted into table_b.
> >
> > I would have posted this in the newsgroup, but I'm at work now
> > and I don't have access to the newsgroups.
> >
> > Thanks.
> >
> > Ken
>
> No Problem!
>
> Try this...
>
> insert into TABLE_B
> select
> SOME_SEQ.NEXTVAL,
> TABLE_A.IDENTIFIER_ID,
> 'SOME VALUE'
> from
> TABLE_A
> where
> TABLE_A.IDENTIFIER_ID NOT IN
> (
> select
> IDENTIFIER_ID
> from
> TABLE_B
> )
> ;
>
> commit;
>
> NOTE:- The "NOT IN" clause may not be the most efficient (performance wise) way to do this.
> But since you have just 30 rows this should work like charm!
>
> Naresh.

Ken,

Sorry for the typo!

The where clause should be ...

where

                 TABLE_A.IDENTIFIER_ID NOT IN


>                 (

>                 select

>                         FK_IDENTIFIER_ID ( and not IDENTIFIER_ID )

>                 from

>                         TABLE_B

>                 )

Naresh Received on Wed Aug 07 1996 - 00:00:00 CEST