Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: How to update columns in one table which will be referenced from another table column

    Hi,

    I have one requirement like below.I am using DB2 Z/OS 10 for this.


    I have to update some flag values with some column values from one table
    which will be referenced from another master table.


    1) First i am fetching records from master table using condition.
    2) Then updating few column values in child table with the master table data
    3) Once values are updated into child table again i have to reset the column
    values into master table with previous flag.

    Currently i am using cursor logic to fetch first records from master table into variables.
    Then updating the child table column by assigning the variable values.
    Then again reseting the master table column values to the previous state.

    By using cursor logic i am not able to update thousands of records in one time.

    Can anyone please suggest some other logic to update child table records at a time.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For 1) and 2) only, a MERGE statement may be enough.

    For 1), 2) and 3), consider to use common-table-expressions and to use data-change-table-reference inside them,
    like "OLD TABLE(searched UPDATE statement)".

    DB2 10 - DB2 SQL - table-reference

    DB2 10 - DB2 SQL - common-table-expression

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My ideas were...

    For 1) and 2) only, a MERGE statement may be enough.
    Like this...
    Code:
    MERGE INTO
          child_table  AS t/*arget*/
    USING master_table AS s/*ource*/
     ON   s.match_col1 = t.match_col1
      AND s.match_col2 = t.match_col2
      ...
    WHEN MATCHED THEN
    UPDATE
     SET   (col_1 , col_2 , ... )
         = (VALUES (s.col_1 , s.col_2 , ... ) )

    For 1), 2) and 3), consider to use common-table-expressions and to use data-change-table-reference inside them,
    like "OLD TABLE(searched UPDATE statement)".
    3) Once values are updated into child table again i have to reset the column
    values into master table with previous flag.
    I thought "... i have to reset the column values into master table with previous flag"
    might be "with previous flag of updated columns of child table"

    If my guess was right,
    MERGE statement might be not usefull now, to know the old values of updated columns of child table.
    Because, OLD TABLE(MERGE ... ) is not supported.(only FINAL TABLE(MERGE ... ) was suppored.)
    data-change-table-reference:

    Code:
    >>-+-FINAL TABLE--(INSERT statement)---------------+------------>
       +-+-FINAL-+--TABLE--(searched UPDATE statement)-+   
       | '-OLD---'                                     |   
       +-OLD TABLE--(searched DELETE statement)--------+   
       '-FINAL TABLE--(MERGE statement)----------------'   
    
    >--+--------------------+--------------------------------------><
       '-correlation-clause-'
    DB2 10 - DB2 SQL - table-reference

    Then I thought another way, like...
    Code:
    WITH
     s AS (
    SELECT *
     FROM  master_table
     WHERE <some conditions>
    )
    , update_child AS (
    SELECT *
     FROM  OLD TABLE
           (UPDATE child_table AS t
             SET   (col_1 , col_2 , ... )
                 = (SELECT col_1 , col_2 , ...
                     FROM  s
                     WHERE s.match_col1 = t.match_col1
                       AND s.match_col2 = t.match_col2
                       ...
                   )
             WHERE EXISTS
                 = (SELECT 0
                     FROM  s
                     WHERE s.match_col1 = t.match_col1
                       AND s.match_col2 = t.match_col2
                       ...
                   )
           )
    )
    SELECT COUNT(*)
     FROM  FINAL TABLE
           (MERGE INTO
                  master_table AS m
            USING update_child AS c
             ON   c.match_col1 = m.match_col1
              AND c.match_col2 = m.match_col2
              ...
            WHEN MATCHED THEN
            UPDATE
             SET   (col_1 , col_2 , ... )
                 = (VALUES (m.col_1 , m.col_2 , ... ) )
           )
    ;
    Those are just my idea.

    To clarify your issue and to test my idea,
    I hope strongly you( rkp ) to provide CREATE TABLE satements and INSERT statements to populate the tables.
    And the results of tables from the data.
    Last edited by tonkuma; 06-29-13 at 16:16.

  4. #4
    Join Date
    Aug 2012
    Posts
    24
    Thank you so much for your help.

    I am trying to use MERGE logic first to update the column values in child table.

    Then directly i will update the flag value in master table.

    But i am getting error while writing the merge syntax.

    SYNTAX:-

    MERGE INTO
    CHILD_TABLE AS T2
    USING MASTER_TABLE AS T1
    ON T2.COL1=T1.T_ADD_COL1
    AND T2.COL2=T1.COL2
    WHEN MATCHED THEN
    UPDATE
    SET T2.col3 = T1.col3 ,
    T2.col4 = (CASE WHEN T1.FLAG = 'Y' THEN 'N' ELSE T2.Col2 END)

    ERROR:-

    Error during Prepare
    42601(-104)[IBM][CLI Driver][DB2] SQL0104N An unexpected token "MASTER_TABLE " was found following "MASTER_TABLE". Expected tokens may include: "(". SQLSTATE=42601


    Thanks in Advance.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see Information Center.
    It looks that source-table in MERGE on DB2 for z/OS was supported only VALUES clause.

    DB2 10 - DB2 SQL - MERGE
    source-table:

    Code:
    >>-(VALUES-+-| values-single-row |-------+-)-------------------->
               |                         (1) |     
               '-| values-multiple-row |-----'     
    
                                   .-,-----------.     
       .-AS-.                      V             |     
    >--+----+--correlation-name--(---column-name-+-)---------------><
    An alternative might be an UPDATE statement, like...

    Code:
    UPDATE CHILD_TABLE AS t/*arget*/
     SET   (col3 , col4)
         = (SELECT s.col3
                 , CASE WHEN s.FLAG = 'Y' THEN 'N' ELSE t.Col2 END
             FROM  MASTER_TABLE AS s/*ource*/
             WHERE s.T_ADD_COL1 = t.COL1
               AND s.COL2       = t.COL2 
           )
     WHERE EXISTS
           (SELECT 0
             FROM  MASTER_TABLE AS s/*ource*/
             WHERE s.T_ADD_COL1 = t.COL1
               AND s.COL2       = t.COL2 
            )
    ;

  6. #6
    Join Date
    Aug 2012
    Posts
    24
    Thank you so much...

    Instead of going with Merge..i have approached 2nd method to update table.

    It is working fine.

Posting Permissions

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