DB2 Compound Query

cruise1 · #1 (**permalink**) 01-21-12, 19:27

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');

tonkuma · #2 (**permalink**) 01-21-12, 20:00

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'
       )
;

tonkuma · #3 (**permalink**) 01-21-12, 20:53

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' 
;

tonkuma · #4 (**permalink**) 01-21-12, 21:14

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.

cruise1 · #5 (**permalink**) 01-21-12, 21:55

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.

tonkuma · #6 (**permalink**) 01-22-12, 01:56

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.

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech