-
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
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|