Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Lightbulb Unanswered: DB2 Compound Query

    Appreciate if someone could look at the following & come up with an UPDATE query

    Table A - IPMA

    BUSSINESS_ENTITY (pk)
    PRICE_TYPE (pk)
    PRICE_ID (pk)
    DATE_TIME
    USERID
    BUSN_ENT
    ITEM_TYPE
    ITEM_ID
    TYPE
    SUPPLIER
    STATUS
    STATUS_DATETIME

    Table B - IPDT

    BUSINESS_ENTITY (pk)
    PRICE_TYPE (pk)
    PRICE_ID (pk)
    BUS_ENT (pk)
    TYPE (pk)
    CODE
    EFF_START
    DATETIME
    USERID
    EFF_STOP
    ENTER_DATE
    PRICE
    STATUS
    STATUS_DATETIME
    REFERENCE

    The ITEM_ID (from table: IPMA) is ONE-to-MANY, with respect to EFF_START & EFF_STOP (from table: IPDT)

    Table IPDT has = 980,000 rows & The following query returns = 2500 rows.

    Now, I need a SINGLE UPDATE query, to update EFF_STOP of 2500 rows, on table: IPDT.

    SELECT A.ITEM_ID,
    B.EFF_START,
    B.EFF_STOP
    FROM IPMA A,
    IPDT B
    WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY
    AND A.PRICE_TYPE = B.PRICE_TYPE
    AND A.PRICE_ID = B.PRICE_ID
    AND B.EFF_START =
    (SELECT MAX(C.EFF_START)
    FROM IPDT C
    WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY
    AND C.PRICE_TYPE = B.PRICE_TYPE
    AND C.PRICE_ID = B.PRICE_ID)
    AND B.EFF_STOP BETWEEN '2011-06-30' AND '9999-06-30'
    AND B.EFF_STOP <> '9999-06-30');

    With query below, trying to UPDATE, a certain range MAX(EFF_START) and EFF_STOP (BETWEEN '2011-06-30' AND '9999-06-30') and SET EFF_STOP to: '9999-06-30, which are not equal to '9999-06-30'.

    But, it is updating around 89000 rows using EXIXTS updates ALL rows. But expected to update 2500 rows only.

    And I think, as in SELECT statement above, I need to use the ITEM_ID predicate (from table: IPMA), but could not refer/use multiple tables on UPDATE statement (our DB2 version is 8.1).

    UPDATE IPDT
    SET EFF_STOP = '9999-06-30'
    WHERE EFF_STOP IN
    (SELECT B.EFF_STOP
    FROM IPMA A,
    IPDT B
    WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY
    AND A.PRICE_TYPE = B.PRICE_TYPE
    AND A.PRICE_ID = B.PRICE_ID
    AND B.EFF_START =
    (SELECT MAX(C.EFF_START)
    FROM IPDT C
    WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY
    AND C.PRICE_TYPE = B.PRICE_TYPE
    AND C.PRICE_ID = B.PRICE_ID)
    AND B.EFF_STOP BETWEEN '2011-06-30' AND '9999-06-30'
    AND B.EFF_STOP <> '9999-06-30');

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to use primary key to specify rows to be updated, like...

    Example 1:
    Code:
    UPDATE IPDT 
       SET EFF_STOP = '9999-06-30' 
     WHERE /*EFF_STOP*/
           (  BUSINESS_ENTITY -- (pk) 
            , PRICE_TYPE      -- (pk) 
            , PRICE_ID        -- (pk) 
            , BUS_ENT         -- (pk) 
            , TYPE            -- (pk) 
           )
           IN
           (SELECT /*B.EFF_STOP*/
                   B.BUSINESS_ENTITY -- (pk) 
                 , B.PRICE_TYPE      -- (pk) 
                 , B.PRICE_ID        -- (pk) 
                 , B.BUS_ENT         -- (pk) 
                 , B.TYPE            -- (pk) 
             FROM  IPMA A
                 , IPDT B 
             WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
               AND A.PRICE_TYPE      = B.PRICE_TYPE 
               AND A.PRICE_ID        = B.PRICE_ID 
               AND B.EFF_START = 
                   (SELECT MAX(C.EFF_START) 
                     FROM  IPDT C 
                     WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
                       AND C.PRICE_TYPE      = B.PRICE_TYPE 
                       AND C.PRICE_ID        = B.PRICE_ID
                   ) 
               AND B.EFF_STOP BETWEEN '2011-06-30' AND '9999-06-30' 
               AND B.EFF_STOP <> '9999-06-30'
           )
    ;
    Last edited by tonkuma; 01-21-12 at 21:40. Reason: Add name "Example 1" to the sample update statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example.

    Example 2:
    Code:
    UPDATE IPDT
       SET EFF_STOP = '9999-06-30' 
     WHERE /*EFF_STOP*/
           (  BUSINESS_ENTITY -- (pk) 
            , PRICE_TYPE      -- (pk) 
            , PRICE_ID        -- (pk) 
            , BUS_ENT         -- (pk) 
            , TYPE            -- (pk) 
           )
           IN
           (SELECT BUSINESS_ENTITY -- (pk) 
                 , PRICE_TYPE      -- (pk) 
                 , PRICE_ID        -- (pk) 
                 , BUS_ENT         -- (pk) 
                 , TYPE            -- (pk) 
             FROM  (SELECT BUSINESS_ENTITY -- (pk) 
                         , PRICE_TYPE      -- (pk) 
                         , PRICE_ID        -- (pk) 
                         , BUS_ENT         -- (pk) 
                         , TYPE            -- (pk)
                         , ROW_NUMBER()
                              OVER( PARTITION BY BUSINESS_ENTITY
                                               , PRICE_TYPE
                                               , PRICE_ID
                                        ORDER BY EFF_START DESC
                                  ) AS rnum
                     FROM  IPDT B 
                     WHERE EXISTS(
                           SELECT 0
                            FROM  IPMA A
                            WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
                              AND A.PRICE_TYPE      = B.PRICE_TYPE 
                              AND A.PRICE_ID        = B.PRICE_ID
                           )
                   ) B
             WHERE rnum = 1
               AND EFF_STOP BETWEEN '2011-06-30' AND DATE('9999-06-30') - 1 DAY 
           )
    ;
    If there is a corresponding row in IPMA for all rows of IPDT, you may want to remove EXISTS predicate.
    I thought this might be your case.
    Because, you wrote there is "ONE-to-MANY" relationship between IPMA and IPDT
    (Although colums were different).


    If you upgraded to DB2 8.2 or later, you would be able to use UPDATE (fullselect), like...

    Example 3:
    Code:
    UPDATE
           (SELECT *
             FROM  (SELECT BUSINESS_ENTITY -- (pk) 
                         , PRICE_TYPE      -- (pk) 
                         , PRICE_ID        -- (pk) 
                         , BUS_ENT         -- (pk) 
                         , TYPE            -- (pk)
                         , EFF_STOP
                         , ROW_NUMBER()
                              OVER( PARTITION BY BUSINESS_ENTITY
                                               , PRICE_TYPE
                                               , PRICE_ID
                                        ORDER BY EFF_START DESC
                                  ) AS rnum
                     FROM  IPDT B 
                     WHERE EXISTS(
                           SELECT 0
                            FROM  IPMA A
                            WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
                              AND A.PRICE_TYPE      = B.PRICE_TYPE 
                              AND A.PRICE_ID        = B.PRICE_ID
                           )
                   ) B
             WHERE rnum = 1
               AND EFF_STOP BETWEEN '2011-06-30' AND DATE('9999-06-30') - 1 DAY 
           )
       SET EFF_STOP = '9999-06-30' 
    ;
    Last edited by tonkuma; 01-21-12 at 22:04. Reason: Move a predicate "AND B.EFF_STOP BETWEEN ..." to one higher subquery.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I assumed that your DB2 was DB2 for LUW.

    If your DB2 was on z/OS,
    ROW_NUMBER() was supported from DB2 9.1 and UPDATE (fullselect) is not supported.

  5. #5
    Join Date
    Jan 2012
    Posts
    2
    Tonkuma san - AWESOME, example-1 did work, as expected. Appreciate & Thank you very much.

    Infact our DB2 v8.1 is on z/OS - upgrading to 9.1, this summer.

    I will keep your other queries for my future reference. Thanks again & Regards.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Minor two variations showed in Example 2 might be applied to Example 1, too.

    (1) Access to IPMA might be re-written by EXISTS predicate and the following note might be applied.
    If there is a corresponding row in IPMA for all rows of IPDT, you may want to remove EXISTS predicate.
    I thought this might be your case.
    Because, you wrote there is "ONE-to-MANY" relationship between IPMA and IPDT
    (Although colums were different).
    (2) A predicate for EFF_STOP(DATE datatype was assumed for EFF_STOP)


    Example 1a:
    Code:
    UPDATE IPDT 
       SET EFF_STOP = '9999-06-30' 
     WHERE /*EFF_STOP*/
           (  BUSINESS_ENTITY -- (pk) 
            , PRICE_TYPE      -- (pk) 
            , PRICE_ID        -- (pk) 
            , BUS_ENT         -- (pk) 
            , TYPE            -- (pk) 
           )
           IN
           (SELECT /*B.EFF_STOP*/
                   BUSINESS_ENTITY -- (pk) 
                 , PRICE_TYPE      -- (pk) 
                 , PRICE_ID        -- (pk) 
                 , BUS_ENT         -- (pk) 
                 , TYPE            -- (pk) 
             FROM  IPDT B 
             WHERE EXISTS(
                   SELECT 0
                    FROM  IPMA A
                    WHERE A.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
                      AND A.PRICE_TYPE      = B.PRICE_TYPE 
                      AND A.PRICE_ID        = B.PRICE_ID
                   )
               AND EFF_START = (
                   SELECT MAX(C.EFF_START) 
                    FROM  IPDT C 
                    WHERE C.BUSINESS_ENTITY = B.BUSINESS_ENTITY 
                      AND C.PRICE_TYPE      = B.PRICE_TYPE 
                      AND C.PRICE_ID        = B.PRICE_ID
                   ) 
               AND EFF_STOP BETWEEN '2011-06-30' AND DATE('9999-06-30') - 1 DAY 
           )
    ;

Posting Permissions

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