asked on

Oracle SQL - NOT EXISTS - Preventing records from being INSERTed twice

Hello, I have the following query which inserts data into one table with data from another table, as follows:

INSERT INTO TABLE1
( TA_CD
,TA_PROD_CD
,PROD_CD
,ENTRY_USR
,UPD_USR
,NO_PROCESS_FLG )
SELECT 'FID'
,PROD_CUSIP
,PROD_CD
,'JHAXXX'
,'JHAXXX'
,'N'
FROM TABLE2
WHERE PROD_TYPE in ('MM','MF','IN','LS')
AND TRIM(SHARE_CLASS) <> 'Z'
AND PROD_CUSIP IS NOT NULL
AND PROD_STAT IN ('O','CL')

Pretend there isn't a primary key on table TABLE1. If this SQL was run twice, I want to prevent the same data from being inserted into table TABLE1 twice. I know my solution involves a NOT EXISTS; I just don't know how to write it.

Thank you.

tigin44

TRY

INSERT INTO TABLE1
    (    TA_CD
        ,TA_PROD_CD
        ,PROD_CD
        ,ENTRY_USR
        ,UPD_USR
        ,NO_PROCESS_FLG )
    SELECT   'FID'
            ,PROD_CUSIP
            ,PROD_CD
            ,'JHAXXX'
            ,'JHAXXX'
            ,'N'
    FROM    TABLE2 T2
    WHERE   PROD_TYPE in ('MM','MF','IN','LS')
    AND     TRIM(SHARE_CLASS) <> 'Z'
    AND     PROD_CUSIP IS NOT NULL
    AND     PROD_STAT IN ('O','CL')
    AND NOT EXISTS(SELECT NULL FROM TABLE1 T1 WHERE T1.TA_PROD_CD = T2.TA_PROD_CD)

Open in new window

slightwv (䄆 Netminder)

I would probably recommend 'MINUS'. You would need to see which one had better performance.

INSERT INTO TABLE1
    (    TA_CD         ,TA_PROD_CD
        ,PROD_CD         ,ENTRY_USR
        ,UPD_USR         ,NO_PROCESS_FLG )
    SELECT   'FID'             ,PROD_CUSIP
            ,PROD_CD             ,'JHAXXX'
            ,'JHAXXX'             ,'N'
    FROM    TABLE2
    WHERE   PROD_TYPE in ('MM','MF','IN','LS')
    AND     TRIM(SHARE_CLASS) <> 'Z'
    AND     PROD_CUSIP IS NOT NULL
    AND     PROD_STAT IN ('O','CL')
minus
    select     TA_CD         ,TA_PROD_CD
        ,PROD_CD         ,ENTRY_USR
        ,UPD_USR         ,NO_PROCESS_FLG
from TABLE1

Open in new window

DoubleStroke

ASKER

tigin44, there isn't a field called TA_PROD_CD in TABLE2

tigin44

sory I missed...
try this

INSERT INTO TABLE1
    (    TA_CD
        ,TA_PROD_CD
        ,PROD_CD
        ,ENTRY_USR
        ,UPD_USR
        ,NO_PROCESS_FLG )
    SELECT   'FID'
            ,PROD_CUSIP
            ,PROD_CD
            ,'JHAXXX'
            ,'JHAXXX'
            ,'N'
    FROM    TABLE2 T2
    WHERE   PROD_TYPE in ('MM','MF','IN','LS')
    AND     TRIM(SHARE_CLASS) <> 'Z'
    AND     PROD_CUSIP IS NOT NULL
    AND     PROD_STAT IN ('O','CL')
    AND NOT EXISTS(SELECT NULL FROM TABLE1 T1 WHERE T1.TA_PROD_CD = T2.PROD_CUSIP)

Open in new window

DoubleStroke

ASKER

SlightWV, I sort of lied. There is a primary key on the table and when I run your code I'm getting a primary key violation. That being said, I realize that a primary key will prevent duplicates from being inserted. But the whole point of this is to prevent any errors from arising while running the statement. A third party DBA is going to run this script and I felt it best to eliminate any errors from the output script, even if it's a primary key violation.

So MINUS will not work in this situation right? I apologize for not explaining the situation up front.

slightwv (䄆 Netminder)

>>So MINUS will not work in this situation right?

Either approach should still work. You just need to add the appropriate where clause in either.

If you are fully confused, give us sample table defs, sample data and expected results and I'm sure we'll provide 100% working examples.

Are updates allowed? Now, the question is:
Table1 and table2 look like: (col1 PK, col2).

You insert a row into table2: 'A','1'. You run your insert then someone updates 'A' in table1 to: 'A',2'. Since you have a primary key and you only want inserts, you'll miss the update.

>>I apologize for not explaining the situation up front.

15 demerits for not fully explaining the situation!!! ;)

ASKER CERTIFIED SOLUTION

shru_0409

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hans_vd

Oracle has a construct especially for doing what you want: the MERGE statement.
In case you need it, you can add a WHEN MATCHED THEN UPDATE... clause

Your statement will look like this:

MERGE INTO table1 t1
USING (
SELECT prod_cusip, prod_cd
FROM table2 t2
WHERE prod_type IN('MM', 'MF', 'IN', 'LS')
AND TRIM(share_class) <> 'Z'
AND prod_cusip IS NOT NULL
AND prod_stat IN('O', 'CL')
ON (t1.ta_prod_cd = t2.prod_cusip AND t1.prod_cd = t2.prod_cd)
WHEN NOT MATCHED THEN
INSERT (ta_cd, ta_prod_cd, prod_cd, entry_usr, upd_usr, no_process_flg)
VALUES ('FID', t2.ta_prod_cd, t2.prod_cd, 'JHAXXX', 'JHAXXX', 'N')

DoubleStroke

ASKER

Folks, I apologize for the delay in getting back to you. I realize it's not good form not to be timely, but different priorities at work have shifted focus. I hope to get back to this within a day or two.

DoubleStroke

ASKER

You missed "t1.TA_CD = 'FID' in the SELECT in the NOT EXISTS clause but I get the point. Well done.