insert and not exists

Insert into ProjectCode(ProjectCode_Item_Code)
SELECT ProjectCode_Item_Code
FROM ProjectCode
WHERE NOT EXISTS(SELECT ProjectCode_Item_Code
FROM ProjectCode where ProjectCode_Item_Code = xx) ;

I want to check if the row already exists in the table, if so then don't do anything but if it does not exists then insert the row. Above is just an example of what I am trying to do.

The problem is when this table does not have any records, its empty then
NOT EXISTS(SELECT ProjectCode_Item_Code
FROM ProjectCode where ProjectCode_Item_Code = xx)
will not return anything , then will my query work ?


Thanks
Sonali

Quote:

---

Originally posted by sonaliak
The problem is when this table does not have any records, its empty then
NOT EXISTS(SELECT ProjectCode_Item_Code
FROM ProjectCode where ProjectCode_Item_Code = xx)
will not return anything

---

Wrong, in this case the above expression will return TRUE, so the inserts will take place.

Sure, it will work.


F.

The query works, but returns nothing, then no rows are created.


SQL> create table tab (col number);

Table created.

SQL> insert into tab (col)
2 select col
3 from tab
4 where not exists (select col from tab where col = 1);

0 rows created.



F.

As the matching row does not exists obviously because the table is empty, I want it to insert that row, but it doesn't...

Is there something wrong with my query ?

Thanks for help
Sonali

Am I being stupid here, or is it possible that the reason that this does not work is because you are trying to insert into "Table A" all the records in "Table A" that do not exist in "Table A"? If they do not exist in "Table A" then you can't select them from "Table A" in order to insert them. Or have I missed something?

Quote:

---

Originally posted by slimdave
Am I being stupid here, or is it possible that the reason that this does not work is because you are trying to insert into "Table A" all the records in "Table A" that do not exist in "Table A"? If they do not exist in "Table A" then you can't select them from "Table A" in order to insert them. Or have I missed something?

---

Yeah, uh, what *he* said...

I think...

(scratches head and walks away)

:D

- Chris

[Edited by chrisrlong on 09-19-2002 at 02:01 PM]

Of course there will be nothing to insert into table when table is empty.



1 select
2 cust_no
3* from qbfrc200_1_tmp
SQL> /

no rows selected <<< EMPTY TABLE



1 select
2 cust_no
3 from qbfrc200_1_tmp
4 where not exists
5 (
6 select
7 cust_no
8 from qbfrc200_1_tmp
9* )
SQL> /

no rows selected <<< SINCE TABLE IS EMPTY - NO ROW IS RETURNED


1 insert into qbfrc200_1_tmp(cust_no)
2 select
3 cust_no
4 from qbfrc200_1_tmp
5 where not exists
6 (
7 select
8 cust_no
9 from qbfrc200_1_tmp
10* )
SQL> /

0 rows created. <<< THEREFORE NO ROW IS INSERTED

you asked if your query works. Yes, it works, but ..... :)


F.

I guess I did not explain the question very well...
I will try again..
At 1st the table is empty..
I want to insert a row coming in..obviously at this point as there are no records in the table.. I want to insert the 1st row.

When someone tries to insert the next record... i want to check if it is already there in that table or not.. if its there then do not insert it, if it isn't then insert it.. and so on.

In sql server you can do it by saying

If not exists( SELECT ProjectCode_Item_Code
FROM ProjectCode where ProjectCode_Item_Code = XYZ)
Insert into ProjectCode(ProjectCode_Item_Code)
VALUES(xyz)


can I do the same thing in Oracle ?

The statement I wrote will work and works only when I have atleast 1 record in the table... but not at start when there will be no records.


Insert into ProjectCode(ProjectCode_Item_Code)
SELECT xyz
FROM ProjectCode
WHERE NOT EXISTS(SELECT ProjectCode_Item_Code
FROM ProjectCode where ProjectCode_Item_Code = XYZ) ;



[Edited by SONALIAK on 09-24-2002 at 10:20 AM]

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

Thanks, it works great