one Insert and Update query using two tables

Billa007 · #1 (**permalink**) 10-12-11, 08:25

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

stolze · #2 (**permalink**) 10-12-11, 09:50

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).

Billa007 · #3 (**permalink**) 10-13-11, 10:40

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;

stolze · #4 (**permalink**) 10-14-11, 07:27

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech