insert and not exists - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: insert and not exists

  1. #11
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The fundamental problem is that you are SELECTing your new value from the PROJECTCODE table. This will, as you noted, not work if there are no rows in the table. Further, this will get more and more expensive as the table gets larger. Basically, you never want to select a constant from a real table - that is what DUAL is there for.

    What you want to do is:
    Code:
    INSERT INTO
       PROJECTCODE
          (
          PROJECTCODE_ITEM_CODE
          )
    SELECT
       :PROJECTCODE_ITEM_CODE
    FROM
       DUAL 
    WHERE
       NOT EXISTS(
          SELECT
             1
          FROM
             PROJECTCODE
          WHERE
             PROJECTCODE_ITEM_CODE = :PROJECTCODE_ITEM_CODE
          )   ;
    Of course, a unique index on PROJECTCODE_ITEM_CODE would have done the same thing, albeit a little more expensively.
    Of course, you will want to index that column anyway to avoid a tablescan, and since the value should only be there once, it should be unique as well. So, in the end, the sub-select was not functionally necessary, although it is, as I said, faster than the straight insert.

    Enjoy,

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  2. #12
    Join Date
    Jan 2001
    Posts
    318
    Thanks, it works great
    Sonali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width