Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: problem with insert query

    hi, i have some problem with query..i have 2 different table and i collect data from this table into new table. below this is the query that i already created

    INSERT INTO KNOWLEDGE_BASE (SERIAL, USERID, NODE, NODEALIAS, SUMMARY, ALERTGROUP, FIRSTOCCURRENCE, LASTOCCURRENCE, TEXT1, TEXT2, TEXT3, TEXT4, TEXT5, TEXT6, TEXT7, TEXT8, TEXT9, TEXT10, TEXT11, TEXT12, TEXT13, TEXT14, TEXT15, TEXT16, CHRONO, CUSTOMER)

    SELECT b.SERIAL, b.USERID, a.NODE, a.NODEALIAS, a.SUMMARY, a.ALERTGROUP, a.FIRSTOCCURRENCE, a.LASTOCCURRENCE, b.TEXT1, b.TEXT2, b.TEXT3, b.TEXT4, b.TEXT5, b.TEXT6, b.TEXT7, b.TEXT8, b.TEXT9, b.TEXT10, b.TEXT11, b.TEXT12, b.TEXT13, b.TEXT14, b.TEXT15, b.TEXT16, b.CHRONO, a.CUSTOMER
    FROM REPORTER_STATUS a, REPORTER_JOURNAL b
    where b.serial = a.serial;

    this query is running without error and i successful collect the data i want into the new table,this query only insert data that exists in those two table but after i run this query, the new data in the this two table is not updated to the new table..if i run this query again then the same data from those two table is inserting again and now i have trouble with duplicated data. how to add some condition on this query where only new data from reporter_journal and reporter_status only inserted to new table?

    i try adding condition but have some error:

    where ( select serial from knowledge_base where serial not like serial)
    and b.serial = a.serial

    can someone help me.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Add this to your WHERE cluase:
    AND a.serial NOT IN
    (SELECT serial FROM KNOWLEDGE_BASE)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    hi aflorin27: sorry can u show the full command.

    i try this but error 42601

    where b.serial = a.serial AND NOT IN a.serial (select from knowledge_base)

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    WHERE b.serial = a.serial
    AND a.serial NOT IN
    (SELECT serial FROM KNOWLEDGE_BASE)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    i try with your idea but error appear.. error -104 sqlstate=42601

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Check your statement, as you seem to have a syntax error: https://www-304.ibm.com/support/docv...id=swg21224723
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb The different idea

    I can recomment to you try to use the following query:

    Code:
    INSERT 
    INTO KNOWLEDGE_BASE (SERIAL, USERID, NODE, NODEALIAS, SUMMARY, 
    ALERTGROUP, FIRSTOCCURRENCE, LASTOCCURRENCE, TEXT1, TEXT2, TEXT3, TEXT4, 
    TEXT5, TEXT6, TEXT7, TEXT8, TEXT9, TEXT10, TEXT11, TEXT12, TEXT13, TEXT14, 
    TEXT15, TEXT16, CHRONO, CUSTOMER)
    With Insert_Table as 
    (
    SELECT 
    b.SERIAL, b.USERID, a.NODE, a.NODEALIAS, a.SUMMARY, a.ALERTGROUP, a.FIRSTOCCURRENCE, a.LASTOCCURRENCE, b.TEXT1, b.TEXT2, b.TEXT3, b.TEXT4, b.TEXT5, b.TEXT6, b.TEXT7, b.TEXT8, b.TEXT9, b.TEXT10, b.TEXT11, b.TEXT12, b.TEXT13, b.TEXT14, b.TEXT15, b.TEXT16, b.CHRONO, a.CUSTOMER
    FROM REPORTER_STATUS a, REPORTER_JOURNAL b
    where b.serial = a.serial
    AND a.serial NOT IN (SELECT serial FROM KNOWLEDGE_BASE) 
    )
    select * from insert_table
    Lenny

Tags for this Thread

Posting Permissions

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