insert and not exists
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: insert and not exists

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    Sure, it will work.


    F.

  4. #4
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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]
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Dec 2000
    Posts
    126
    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

  9. #9
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


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


    F.

  10. #10
    Join Date
    Jan 2001
    Posts
    318
    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]
    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