insert and not exists

[sonaliak]

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

[jmodic]

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.

[Mnemonical]

Sure, it will work.


F.

[Mnemonical]

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.

[sonaliak]

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

[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?

[chrisrlong]

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)



- Chris

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

[culonbu]

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

[Mnemonical]

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


F.

[sonaliak]

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]