Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    41

    Unanswered: DB2 Insert statement not working for me

    Hello,

    I am trying to get the below insert statement to work but it is coming back with the error below

    sqlcode: -206

    sqlstate: 42703


    Can anyone offer any advice ?

    Code:
    INSERT INTO table1 (DATE,ID,NUMBER,BOOLEAN,FLOAT,TYPE,UNITS) SELECT DATE,ID,NUMBER,BOOLEAN,FLOAT,TYPE,UNITS FROM table2
    where table1.DATE NOT IN (select DATE from table2);

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See error message text.

    SQL0206N

    name is not valid in the context where it is used.

    ...
    SQL0206N

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    you cleverly used reserved words as column names.
    quote them, if you are not clever enough to change them.

    now, why am i being sarcastic?
    why would you do something (use reserve words for column names) when you will continually be required to put the column names in quotes in you sql?
    Last edited by dbzTHEdinosaur; 09-01-12 at 09:06. Reason: added more demeaning sarcasm.
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though I don't know what you want to do,
    I suspect that you might misplaced table1 and table2 in the subselect.

    Code:
    INSERT INTO
           table1
         ( DATE , ID , NUMBER , BOOLEAN , FLOAT , TYPE , UNITS )
    SELECT DATE , ID , NUMBER , BOOLEAN , FLOAT , TYPE , UNITS
     FROM  table2
     WHERE table2.DATE
           NOT IN (SELECT DATE FROM table1)
    ;

  5. #5
    Join Date
    Aug 2012
    Posts
    41
    Thanks very much for all the replies,

    Tonkuma - your query worked for me

    Just for reference the names I posted below were not the actual real names of the fields. I just used those as reference names. I suppose I should have put more thought into the names I chose.

    I am delighted now that it successfully works !

Posting Permissions

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