Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Update query with conditional joins?

    Code:
    create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
    insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
    insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
    insert into tablea values(13,103,1,'2011-06-01','2011-06-18');
    
    create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
    insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
    insert into tableb values(16,102,1,'2011-07-01','2011-07-20');
    i need to update the table TABLEA based on the TABLEB

    1.if PRODUCTID and RECORD_IND matches with TABLEB then update the table TABLEA only with PRODUCT_PID which is from TABLEB
    2.if PRODUCTID matches and RECORD_IND not matches then update both the PRODUCT_PID and RECORD_IND fields from TABLEB.
    3. if PRODUCTID not matches with TABLEB then delete the record from TABLEA.

    thanks,
    laknar

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A single query for all 3 is of course impossible since UPDATE and DELETE cannot go in the same query. So you either need two statements (possibly with IF..ELSE logic, e.g. using SQL PL) or create an UPDATE trigger on TableA which does the delete, e.g. when RECORD_IND of TABLEA is set to NULL.

    B.t.w.: cases 1 and 2 can easily be combined into the single
    12: if PRODUCTID matches then update in table TABLEA both the PRODUCT_PID and RECORD_IND fields from TABLEB.

    If you use the trigger solution, the single statement for all three becomes:
    Code:
    UPDATE tableA a
    SET product_pid=(SELECT product_pid FROM tableB WHERE productid=a.productid),
        record_ind=(SELECT record_ind FROM tableB WHERE productid = a.productid)
    Last edited by Peter.Vanroose; 10-26-11 at 03:58.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here are two examples.
    These statements may not work on other DB2 version/release and platform OS.
    http://www.dbforums.com/db2/854783-m...e-posting.html

    I tested on DB2 Express-C 9.7 fixpack 4 on Windows/XP.
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.4
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.

    Note: I used case 12 by Peter instead of case 1 and case 2, in Example 1 and Example 2.

    Clean up tablea:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DELETE FROM tablea;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
    insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
    insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
    insert into tablea values(13,103,1,'2011-06-01','2011-06-18');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    Example 1: common-table-expressions with data-change-table-reference
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        1          2011-06-01 2011-06-30
             11 101        1          2011-06-01 2011-06-30
             12 102        1          2011-07-01 2011-07-20
             13 103        1          2011-06-01 2011-06-18
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
     update_a AS (
    SELECT COUNT(*) AS update_count
     FROM  FINAL TABLE
           (UPDATE tablea a
               SET (product_pid , record_ind)
                 = (SELECT b.product_pid , b.record_ind
                     FROM  tableb b
                     WHERE b.productid = a.productid
                   )
             WHERE EXISTS
                   (SELECT 0 /* b.product_pid , b.record_ind */
                     FROM  tableb b
                     WHERE b.productid = a.productid
                   )
           ) updt_a
    )
    , delete_a AS (
    SELECT COUNT(*) AS delete_count
     FROM  OLD TABLE
           (DELETE FROM
                   tablea a
             WHERE NOT EXISTS
                   (SELECT 0 /* b.product_pid , b.record_ind */
                     FROM  tableb b
                     WHERE b.productid = a.productid
                   )
           ) delete_a
    )
    SELECT update_count , delete_count
     FROM  update_a
         , delete_a
    ;
    ------------------------------------------------------------------------------
    
    UPDATE_COUNT DELETE_COUNT
    ------------ ------------
               3            1
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             16 102        1          2011-07-01 2011-07-20
    
      3 record(s) selected.

    Clean up tablea again.

    Example 2: MERGE with sysibm.sysdummy1
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        1          2011-06-01 2011-06-30
             11 101        1          2011-06-01 2011-06-30
             12 102        1          2011-07-01 2011-07-20
             13 103        1          2011-06-01 2011-06-18
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          tablea a
    USING sysibm.sysdummy1 s
      ON  s.ibmreqd = 'Y'
    WHEN MATCHED
     AND EXISTS
         (SELECT 0
           FROM  tableb b
           WHERE b.productid = a.productid
         ) THEN
    UPDATE
     SET (product_pid , record_ind)
       = (SELECT b.product_pid , b.record_ind
           FROM  tableb b
           WHERE b.productid = a.productid
         )       
    WHEN MATCHED
     AND NOT EXISTS
         (SELECT 0
           FROM  tableb b
           WHERE b.productid = a.productid
         ) THEN
    DELETE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             16 102        1          2011-07-01 2011-07-20
    
      3 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3: Another MERGE statement.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        1          2011-06-01 2011-06-30
             11 101        1          2011-06-01 2011-06-30
             12 102        1          2011-07-01 2011-07-20
             13 103        1          2011-06-01 2011-06-18
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          tablea a
    USING (SELECT productid
                , product_pid
                , record_ind
                , 'B'
            FROM  tableb
           UNION ALL
           SELECT productid
                , NULLIF(0 , 0)
                , NULLIF(0 , 0)
                , 'A'
            FROM  (SELECT productid
                    FROM  tablea
                   EXCEPT
                   SELECT productid
                    FROM  tableb
                  ) a
          ) b(productid , product_pid , record_ind , table_id)
      ON  b.productid = a.productid
    WHEN MATCHED
     AND table_id = 'B' THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
     AND table_id = 'A' THEN
    DELETE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             16 102        1          2011-07-01 2011-07-20
    
      3 record(s) selected.
    Note: The following subquery may be rewritten by using NOT EXISTS or NOT IN predicate.
    Code:
    ...
           UNION ALL
           SELECT productid
                , NULLIF(0 , 0)
                , NULLIF(0 , 0)
                , 'A'
            FROM  (SELECT productid
                    FROM  tablea
                   EXCEPT
                   SELECT productid
                    FROM  tableb
                  ) a
          ) b(productid , product_pid , record_ind , table_id)
    ...

  5. #5
    Join Date
    Jul 2008
    Posts
    94
    Tonkuma, Thank you so much

    one more scenario there is a possibility of one to many when joining. in that case we need to pick MIN(Record_IND).

    Code:
    create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
    insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
    insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
    insert into tablea values(13,103,1,'2011-06-01','2011-06-18');
    
    create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
    insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
    insert into tableb values(16,102,1,'2011-07-01','2011-07-20');
    insert into tableb values(17,102,0,'2011-07-01','2011-07-20');
    insert into tableb values(18,101,0,'2011-06-01','2011-06-30');

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... there is a possibility of one to many when joining. in that case we need to pick MIN(Record_IND).
    How about product_pid?

    I guessed that you want to pick a row with MIN(Record_IND).
    In the sample data, take this row for productid = 102
    (17,102,0,'2011-07-01','2011-07-20')

    An example: (Not tested)
    Instead of:
    Code:
            FROM  tableb
    use:
    Correction: Remove keyword "DESC"
    Code:
            FROM  (SELECT b.*
                        , ROW_NUMBER()
                             OVER(PARTITION BY productid
                                      ORDER BY record_ind) AS row_num
                    FROM  tableb b
                  ) b
            WHERE row_num = 1
    Last edited by tonkuma; 10-27-11 at 02:32. Reason: Remove keyword "DESC" from sample code.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    http://www.dbforums.com/db2/854783-m...e-posting.html
    ...

    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
    You can get his info using the following commands
    db2level -> to get db2 version and fixpack level
    db2licm -l -> to get the db2 type (WSE, ESE, etc)

    ...
    If you are using DB2 9.7 fixpack 4 for LUW,
    you can use
    1) LISTAGG aggregate function
    2) column name(s) in a pattern expression of a LIKE predicate.

    Example 4:(no one to many when joining)
    Code:
    MERGE INTO
          tablea a
    USING (SELECT b.*
                , LISTAGG( CHAR(productid) , ',' )
                     OVER() AS b_productid_all
                , ROW_NUMBER()
                     OVER() AS row_num
            FROM  tableb b
          ) b
      ON  b.productid = a.productid
      OR  b_productid_all
          NOT LIKE '%' || CHAR(a.productid) || '%'
      AND row_num = 1
    WHEN MATCHED
     AND b.productid =  a.productid THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
     AND b.productid <> a.productid THEN
    DELETE
    ;
    Example 5:(no one to many when joining)
    Code:
    MERGE INTO
          tablea a
    USING (SELECT b.*
                , LISTAGG( CHAR(productid) , ',' )
                     OVER() AS b_productid_all
            FROM  tableb b
          ) b
      ON  b.productid = a.productid
      OR  b_productid_all
          NOT LIKE '%' || CHAR(a.productid) || '%'
      AND b_productid_all
              LIKE CHAR(b.productid) || '%'
    WHEN MATCHED
     AND b.productid =  a.productid THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
     AND b.productid <> a.productid THEN
    DELETE
    ;
    Note: You can remove the condition "AND b.productid <> a.productid" in Example 4 and Example 5,
    because...
    MERGE statement

    ...

    Description

    ...
    ...

    WHEN matching-condition
    Specifies the condition under which the modification-operation or the signal-statement is executed. Each matching-condition is evaluated in order of specification. Rows for which the matching-condition evaluates to true are not considered in subsequent matching conditions.

    ...
    See details in
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows

  8. #8
    Join Date
    Jul 2008
    Posts
    94
    Thank you for your quick reply.

    Code:
    create table tablea(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tablea values(10,100,1,'2011-06-01','2011-06-30');
    insert into tablea values(11,101,1,'2011-06-01','2011-06-30');
    insert into tablea values(12,102,1,'2011-07-01','2011-07-20');
    insert into tablea values(13,103,1,'2011-06-01','2011-06-18');
    
    create table tableb(product_pid integer,productid varchar(10),record_ind varchar(1),start_date date, end_date date);
    
    insert into tableb values(10,100,0,'2011-06-01','2011-06-30');
    insert into tableb values(15,101,0,'2011-06-01','2011-06-30');
    insert into tableb values(16,102,1,'2011-07-01','2011-07-20');
    insert into tableb values(17,102,0,'2011-07-01','2011-07-20');
    insert into tableb values(18,101,2,'2011-06-01','2011-06-30');
    1. we have to use both Productid and Record_ind if a match found then update the corresponding row to Tablea.(column Product_pid)

    2. if only Productid matches and record_ind not matches then update the Product_pid and min(record_ind) from tableb to tablea

    3. if productid does not match then delete the corresponding record in tablea.

    i have a query written to update this. but im stuck with second logic.

    prtoductid 102 for first scenario.
    productid 101 for second scenario.
    productid 100 also for second scenario.
    productid 103 for third scenario.

    Please suggest me.
    Last edited by laknar; 10-27-11 at 00:30.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand intention of your last post.
    I thought that your last post was only repeated original post and your second to last post.

    Anyhow, here is an example.
    If the result was different from your required result,
    please show your required result by the format of result of "SELECT * FROM tablea".

    Example 4-1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          tablea a
    USING (SELECT b.*
                , LISTAGG( CHAR(productid) , ',' )
                     OVER() AS b_productid_list
                , ROW_NUMBER()
                     OVER() AS row_num
                , ROW_NUMBER()
                     OVER( PARTITION BY productid
                               ORDER BY record_ind
                         )  AS rn_record_ind
            FROM  tableb b
          ) b
      ON  b.productid   = a.productid
      AND rn_record_ind = 1
      OR  b_productid_list
          NOT LIKE '%' || CHAR(a.productid) || '%'
      AND row_num = 1
    WHEN MATCHED
     AND b.productid =  a.productid
    THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
    THEN
    DELETE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             17 102        0          2011-07-01 2011-07-20
    
      3 record(s) selected.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example using LISTAGG function.

    Example 6:
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
          tablea a
    USING (SELECT product_pid , productid , record_ind
                , LISTAGG( CHAR(productid) , ',' )
                     OVER() AS b_productid_list
                , ROW_NUMBER()
                     OVER( PARTITION BY productid
                               ORDER BY record_ind
                         )  AS rn_record_ind
            FROM  tableb b
            GROUP BY
                  ROLLUP( (product_pid , productid , record_ind) ) 
          ) b
      ON  b.productid = a.productid
      AND rn_record_ind = 1
      OR  b_productid_list
          NOT LIKE '%' || CHAR(a.productid) || '%'
      AND b.productid IS NULL
    WHEN MATCHED
     AND b.productid =  a.productid
    THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
    THEN
    DELETE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             17 102        0          2011-07-01 2011-07-20
    
      3 record(s) selected.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to reduce number of predicates in ON clause.

    Example 7: Two pewdicates in ON clause.
    Note:
    p1: COALESCE(...) = a.productid
    p2: rn_record_ind = 1
    Code:
    MERGE INTO
          tablea a
    USING (SELECT product_pid , productid , record_ind
                , LISTAGG( productid , ',' )
                     OVER() AS b_productid_list
                , ROW_NUMBER()
                     OVER( PARTITION BY productid
                               ORDER BY record_ind
                         )  AS rn_record_ind
            FROM  tableb b
            GROUP BY
                  ROLLUP( (product_pid , productid , record_ind) ) 
          ) b
      ON  COALESCE( b.productid
                  , CASE LOCATE(a.productid , b_productid_list)
                    WHEN 0 THEN a.productid
                    ELSE        ''
                    END
                  ) = a.productid
      AND rn_record_ind = 1
    WHEN MATCHED
     AND b.productid =  a.productid
    THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
    THEN
    DELETE
    ;
    Last edited by tonkuma; 10-30-11 at 23:59. Reason: Add Note.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Shouldn't the second "WHEN MATCHED" be "WHEN NOT MATCHED" ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The rows in tablea which were not matched with tableb would be matched with grand-total row of subquery "b".

    For example,
    a row in tablea with productid = 103 doesn't match with tableb,
    but it was matched with grand-total(productid is null) row of subquery "b".

    Sample Data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        1          2011-06-01 2011-06-30
             11 101        1          2011-06-01 2011-06-30
             12 102        1          2011-07-01 2011-07-20
             13 103        1          2011-06-01 2011-06-18
    
      4 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tableb;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             16 102        1          2011-07-01 2011-07-20
             17 102        0          2011-07-01 2011-07-20
             18 101        2          2011-06-01 2011-06-30
    
      5 record(s) selected.
    Modified query to show the result of matching:
    Code:
    ------------------------------ Commands Entered ------------------------------
    --MERGE INTO
    SELECT a.product_pid , a.productid , a.record_ind
         , b.product_pid , b.productid , b.record_ind
         , VARCHAR(b_productid_list , 30) AS b_productid_list
     FROM
          tablea a
    --USING 
     LEFT OUTER JOIN
          (SELECT product_pid , productid , record_ind
                , LISTAGG( productid , ',' )
                     OVER() AS b_productid_list
                , ROW_NUMBER()
                     OVER( PARTITION BY productid
                               ORDER BY record_ind
                         )  AS rn_record_ind
            FROM  tableb b
            GROUP BY
                  ROLLUP( (product_pid , productid , record_ind) ) 
          ) b
      ON  COALESCE( b.productid
                  , CASE LOCATE(a.productid , b_productid_list)
                    WHEN 0 THEN a.productid
                    ELSE        ''
                    END
                  ) = a.productid
      AND rn_record_ind = 1
    /*
    WHEN MATCHED
     AND b.productid =  a.productid
    THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
    THEN
    DELETE
    */
    ;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND PRODUCT_PID PRODUCTID  RECORD_IND B_PRODUCTID_LIST              
    ----------- ---------- ---------- ----------- ---------- ---------- ------------------------------
             10 100        1                   10 100        0          100,101,101,102,102           
             11 101        1                   15 101        0          100,101,101,102,102           
             12 102        1                   17 102        0          100,101,101,102,102           
             13 103        1                    - -          -          100,101,101,102,102           
    
      4 record(s) selected.
    Last edited by tonkuma; 10-31-11 at 06:41.

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    OK, I see.
    Didn't know that MERGE accepts more than one "WHEN MATCHED" clause!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes, MERGE accepts more than one "WHEN MATCHED" clause.

    Here is the result of MERGE statement for data in my previous post(same data supplied by laknar).
    Code:
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO
    /*
    SELECT a.product_pid , a.productid , a.record_ind
         , b.product_pid , b.productid , b.record_ind
         , VARCHAR(b_productid_list , 30) AS b_productid_list
     FROM
    */
          tablea a
    USING 
    -- LEFT OUTER JOIN
          (SELECT product_pid , productid , record_ind
                , LISTAGG( productid , ',' )
                     OVER() AS b_productid_list
                , ROW_NUMBER()
                     OVER( PARTITION BY productid
                               ORDER BY record_ind
                         )  AS rn_record_ind
            FROM  tableb b
            GROUP BY
                  ROLLUP( (product_pid , productid , record_ind) ) 
          ) b
      ON  COALESCE( b.productid
                  , CASE LOCATE(a.productid , b_productid_list)
                    WHEN 0 THEN a.productid
                    ELSE        ''
                    END
                  ) = a.productid
      AND rn_record_ind = 1
    
    WHEN MATCHED
     AND b.productid =  a.productid
    THEN
    UPDATE
       SET (  product_pid ,   record_ind)
         = (b.product_pid , b.record_ind)
    WHEN MATCHED
    THEN
    DELETE
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tablea;
    ------------------------------------------------------------------------------
    
    PRODUCT_PID PRODUCTID  RECORD_IND START_DATE END_DATE  
    ----------- ---------- ---------- ---------- ----------
             10 100        0          2011-06-01 2011-06-30
             15 101        0          2011-06-01 2011-06-30
             17 102        0          2011-07-01 2011-07-20
    
      3 record(s) selected.

Posting Permissions

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