Re: Thanks for the help but...

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. Received on Wed Aug 07 1996 - 00:00:00 CEST