Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: one Insert and Update query using two tables

    Hi;


    Please clarify the possibilites of the one INSERT and UPDATE query with two tables

    Below is the Insert Table query
    Code:
    111R-INSERT.
    
      INSERT INTO TABLE1 (CD_PLT,PRE_NAME,IN_DT,MAX_IN_DT,WKLY_CA,USER_NME)
       
      VALUES(:WS-CD-PLT,:WS-PRE-NAME,:WS-IN-DT,:WS-MAX-IN-DT,:WS-WKLY-CA,:WS-USER-NME)
    
        EVALUATE SQLCODE                  ALSO SQLWARN0
        WHEN DB2-SUCCESSFUL-RETURN    ALSO SPACES  
         CONTINUE  
    END-EVALUATE.
    
    111R-EXIT.EXIT.
    
    112R-INSERT-DETAIL.  
    
    INSERT INTO TABLE2 (CD_PLT,PRE_NAME,IN_DT,POLICY_DET,TMP_STMP)
       
       VALUES(:WS-CD-PLT,:WS-PRE-NAME,:WS-IN-DT,:WS-POLICY-DET,:WS-CURRENT-TME)   
    EVALUATE SQLCODE                  ALSO SQLWARN0
        WHEN DB2-SUCCESSFUL-RETURN    ALSO SPACES  
         CONTINUE                              
     END-EVALUATE.
    
    112R-EXIT.EXIT.
    Below is Update query
    Code:
    113R-UPDATE.
       
    UPDATE  TABLE1
    
     SET IN_DT=:WS-IN-DT
    ,TMP_STMP=:WS-CURRENT-TME
    
    WHERE
    CD_PLT=:WS-CD-PLT
    AND PRE_NAME=:WS-PRE-NAME
    
     EVALUATE SQLCODE                  ALSO SQLWARN0
         WHEN DB2-SUCCESSFUL-RETURN    ALSO SPACES  
              CONTINUE                              
    END-EVALUATE.
    
    113R-EXIT.EXIT.
     
    114R-UPDATE-DETAIL.
    
      UPDATE  TABLE2
     SET IN_DT=:WS-IN-DT
        ,POLICY_DET=:WS-POLICY-DET
    ,TMP_STMP=:WS-CURRENT-TME
    
    WHERE
    CD_PLT=:WS-CD-PLT
    AND PRE_NAME=:WS-PRE-NAME
    AND TURN_ID=0
    AND TMP_STMP=:WS-OLD-TME-STMP
    
     EVALUATE SQLCODE                  ALSO SQLWARN0
         WHEN DB2-SUCCESSFUL-RETURN    ALSO SPACES  
              CONTINUE                              
    END-EVALUATE.
    114R-EXIT.EXIT.
    Please let me know the way of , to do both Insert query of two tables will perform with one INSERT query in the same para.
    And same for Update also.
    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    INSERT and UPDATE work on a single table only. That's the way SQL works. What you could possibly do is to create a view that is a union or join of TABLE1 and TABLE2 and then insert into the view. You may have to create an INSTEAD OF trigger on the view so that the trigger will direct the data to the correct table (either TABLE1 or TABLE2).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply..

    I was calling the both Insert query at the same time..What is the issue i was facing is, the given second row values would not insert in the second table(TABLE2),because
    CD_PLT
    ,PRE_NAME
    ,IN_DT are the keys in Table1

    CD_PLT
    ,PRE_NAME
    ,TMP_STMP are the keys in Table2

    While inserting multiple records at the same time CD_PLT,PRE_NAME are having same values and IN_DT is having distinct values in Table1
    and CD_PLT,PRE_NAME are having same values and POLICY_DET is having distinct values in Table2

    Below columns having same value for both tables while inserting
    CD_PLT -->key for Table1 and Table2
    ,PRE_NAME -->key for Table1 and Table2
    ,IN_DT -->key for Table1 only
    ,TMP_STMP -->key for Table2 only

    so that while inserting multiple records for the both tables,the same TIME STAMP are trying to insert in the Second Table(TABLE2) and ended with ERROR CODE -803,bcoz of TMP_STMP is the key for TABLE2

    What my question is-->Is it possible to increse the Time Stamp after inserting first records for the both tables while inserting same time..need slight difference time stamp value should be inserted

    Thanks;

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'm not sure I understood your problem. So let my try to phrase what I think you try to do:
    - you have a foreign key defined between both tables
    - one of the FK columns is of type TIME or TIMESTAMP
    - when inserting into the tables, you either let DB2 generate the TIME/TIMESTAMP value or your application is going to generate a new timestamp for each INSERT (or UPDATE) statement

    Is that about it? What prevents you from using a host variable into which a single timestamp is stored (generated by DB2 or your application) and then that value is passed to both INSERT/UPDATE statements?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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