Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-01-11
10
Medium Priority
?
664 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:DoubleStroke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26287299
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

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26287387
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

0
 

Author Comment

by:DoubleStroke
ID: 26288312
tigin44, there isn't a field called TA_PROD_CD in TABLE2
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 26

Expert Comment

by:tigin44
ID: 26288321
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

0
 

Author Comment

by:DoubleStroke
ID: 26288857
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26289004
>>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!!!  ;)
0
 
LVL 14

Accepted Solution

by:
shru_0409 earned 1200 total points
ID: 26291936
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')
        AND NOT EXISTS(SELECT '1'
                       FROM table1 t1
                      WHERE t1.ta_prod_cd = t2.prod_cusip and t1.prod_cd = t2.prod_cd)


try this
0
 
LVL 6

Expert Comment

by:hans_vd
ID: 26292523
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')
0
 

Author Comment

by:DoubleStroke
ID: 26303728
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.  
0
 

Author Closing Comment

by:DoubleStroke
ID: 31675727
You missed "t1.TA_CD = 'FID' in the SELECT in the NOT EXISTS clause but I get the point.  Well done.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question