From: nedee@club.innet.be (Nedee & Co)
Subject: Re: SQL Question Insert where not exists
Date: 1996/10/30
Message-ID: <557b6a$2o6@news.be.innet.net>#1/1
references: <01bbc125$a7590380$778577c2@earthbound.u-net.com>
organization: INnet NV (post doesn't reflect views of INnet NV)
newsgroups: comp.databases.oracle.tools



"Paul Routledge" <paul@earthbound.u-net.com> wrote:

>I have a query about an insert statement. I already have a number of
>optional statements I can use to acheive the required insert but which one
>gives best performance and is there a more efficient option.
 
>Basically I want to insert into tableA with a select from tableB and tableC
>where such a row does not already exist in tableA.


>Does anyone know of an efficient INSERT WHERE NOT EXISTS?

I think you can use an outer join on table A.
Your insert statement should look something like this :

INSERT INTO TABLE A
SELECT 
FROM tables A, B and C
where a.key1(+) = b.key1
and a.key2(+) = c.key2
and a.otherfield is null

I'm not sure about this but according to the "consisent database"
principle, it should work.

Peter.

PS : let me know if it worked or why it didn't. Thx.