[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

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.
0
DoubleStroke
Asked:
DoubleStroke
  • 4
  • 2
  • 2
  • +2
1 Solution
 
tigin44Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
DoubleStrokeAuthor Commented:
tigin44, there isn't a field called TA_PROD_CD in TABLE2
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
tigin44Commented:
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
 
DoubleStrokeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
shru_0409Commented:
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
 
hans_vdCommented:
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
 
DoubleStrokeAuthor Commented:
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
 
DoubleStrokeAuthor Commented:
You missed "t1.TA_CD = 'FID' in the SELECT in the NOT EXISTS clause but I get the point.  Well done.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now