If this is not an appropriate ng, let me know and please redirect me.
I am not sure if my question is Oracle specific or if it is OK to ask
Oracle specific questions here.

I have a many to many relationship. I've learned that when that
happens you are to make an intermediate table between the two. So,
I've got a table with 2 fields, an ID from the one side and an ID from
the other.

In my application I am writing all the upserts for all my entities. In
doing so, I've come across my first upsert of this type.

Normally, my upserts looks like this:

BEGIN
UPDATE PROSPECT_INTERESTS
SET PERSON_ID = ProspectID, INTEREST_ID = InterestID
WHERE PERSON_ID = :PersonID AND INTEREST_ID = InterestID;
IF SQL%NOTFOUND THEN
INSERT INTO PROSPECT_INTERESTS
(PROSPECT_ID, INTEREST_ID)
VALUES
(ProspectID, InterestID);
END IF;
END;

In this case, the update statement makes no sense, because all there
is to update is the primary key which identifies the thing in the
first place. So, what is the proper way to say "If it doesn't exist
already insert it, otherwise do nothing?"

On 19 Feb, 16:42, cpisz <christopherp... (AT) gmail (DOT) com> wrote:
You don't mention what version of Oracle you are using but if it is
new enough I would suggest merge:

MERGE INTO PROSPECT_INTERESTS x
USING (
VALUES (..., ...)
) y (PERSON_ID, INTEREST_ID)
ON (x.PERSON_ID, x.INTEREST_ID) = (y.PERSON_ID, y.INTEREST_ID)
WHEN NOT MATCHED THEN
INSERT (PERSON_ID, INTEREST_ID)
VALUES (y.PERSON_ID, y.INTEREST_ID)

If your version don't support merge you could try using dual as in

insert into PROSPECT_INTERESTS
select ...,... from dual x
where not exists (
select 1 from PROSPECT_INTERESTS y
where (y.PERSON_ID, y.INTEREST_ID) = (...,...)
)

I don't use Oracle myself so you might have to adjust the syntax
above.


/Lennart