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 = :pProspectID, INTEREST_ID = :pInterestID
WHERE PERSON_ID = :PersonID AND INTEREST_ID = :pInterestID;
IF SQL%NOTFOUND THEN
   INSERT INTO PROSPECT_INTERESTS
      (PROSPECT_ID, INTEREST_ID)
      VALUES
      (:pProspectID, :pInterestID);
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...@gmail.com> wrote:
> 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 =3D :pProspectID, INTEREST_ID =3D :pInterestID
> WHERE PERSON_ID =3D :PersonID AND INTEREST_ID =3D :pInterestID;
> IF SQL%NOTFOUND THEN
> =A0 =A0INSERT INTO PROSPECT_INTERESTS
> =A0 =A0 =A0 (PROSPECT_ID, INTEREST_ID)
> =A0 =A0 =A0 VALUES
> =A0 =A0 =A0 (:pProspectID, :pInterestID);
> 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?"

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) =3D (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) =3D (...,...)
)

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


/Lennart