Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: better way rewrite this Update statement?

    There are million records that match following 3 updates. I was wondering if there is a better way to re-write the following 3?

    ----------------------------------
    -----Update statement # 1
    ----------------------------------

    MERGE INTO SCHEMA.Table T
    USING (SELECT
    S1.CUSTNO
    ,S1.SURR_KEY
    FROM SCHEMA.SOURCE1 S1
    INNER JOIN
    SCHEMA.SOURCE2 S2
    ON S2.SURR_KEY_2 = S1.SURR_KEY_2
    WHERE S2.AREA_CD = 967
    AND S1.STATUS = 'ACTIVE'
    ) s
    ON (T.CUST_NO = s.CUST_NO)
    AND T.LOCATION IN ('NA','EUR','ASIA')

    WHEN MATCHED THEN
    UPDATE SET
    T.C1 = S1.SURR_KEY

    WITH UR;

    ----------------------------------
    -----Update statement # 2
    ----------------------------------

    MERGE INTO SCHEMA.Table T
    USING (SELECT
    S1.CUSTNO
    ,S1.SURR_KEY
    FROM SCHEMA.SOURCE1 S1
    INNER JOIN
    SCHEMA.SOURCE2 S2
    ON S2.SURR_KEY_2 = S1.SURR_KEY_2
    WHERE S2.AREA_CD = 967
    AND S1.LAST_LOGIN_LOCTN = 'NEW YORK' -- Difference is here from above select query
    ) s
    ON (T.CUST_NO = s.CUST_NO)
    AND T.LOCATION IN ('NA','EUR','ASIA')

    WHEN MATCHED THEN
    UPDATE SET
    T.C2 = S1.SURR_KEY --target table is same but different target column is updated here with same source Key becasue source key may have different value

    WITH UR;

    --------------------------------
    -----Update statement # 3
    -- Before Update#1 all T.C1, T.C2 are 0. LAST Update statement to set all the remaining values that did not match in #1 and #2 to 9999.
    ----------------------------------



    UPDATE SCHEMA.Table T
    SET T.C1 = 9999
    ,T.C2 = 9999
    WHERE
    T.C1 = 0
    AND T.C2 = 0 ;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not so sure,
    but these may be worth to try

    Example 1:
    Code:
    UPDATE SCHEMA.Table T
      SET  (c1 , c2)
         = (SELECT CASE S1.STATUS
                   WHEN 'ACTIVE' THEN
                        S1.SURR_KEY
                   ELSE T.c1
                   END
                 , CASE S.LAST_LOGIN_LOCTN
                   WHEN 'NEW YORK' THEN
                        S.SURR_KEY
                   ELSE T.c2
                   END
             FROM  SCHEMA.SOURCE1 S1
             INNER JOIN 
                   SCHEMA.SOURCE2 S2
              ON   S2.SURR_KEY_2 = S1.SURR_KEY_2
             WHERE S1.CUSTNO  = T.CUST_NO
               AND S2.AREA_CD = 967
               AND
               (   S1.STATUS           = 'ACTIVE'
                OR S1.LAST_LOGIN_LOCTN = 'NEW YORK'
               )
               AND T.LOCATION IN ('NA' , 'EUR' , 'ASIA')
            UNION ALL
            VALUES (9999 , 9999)
            /* assumed all selected S1.SURR_KEY were less than 9999. */
            ORDER BY
                  1 , 2
            FETCH FIRST 1 ROW ONLY
           )
    ;

    Example 2:
    Code:
    UPDATE SCHEMA.Table T
      SET  (c1 , c2)
         = (SELECT COALESCE(
                      CASE S1.STATUS
                      WHEN 'ACTIVE'  THEN
                           S1.SURR_KEY
                      WHEN S1.STATUS THEN
                           T.c1
                      END
                    , f.c1
                   )
                 , COALESCE(
                      CASE S.LAST_LOGIN_LOCTN
                      WHEN 'NEW YORK'         THEN
                           S.SURR_KEY
                      WHEN S.LAST_LOGIN_LOCTN THEN
                           T.c2
                      END
                    , f.c2
                   )
             FROM  (VALUES (9999 , 9999) ) AS f(c1 , c2)
             LEFT  OUTER JOIN
                   SCHEMA.SOURCE1 S1
             INNER JOIN 
                   SCHEMA.SOURCE2 S2
              ON   S2.SURR_KEY_2 = S1.SURR_KEY_2
              ON   S1.CUSTNO  = T.CUST_NO
               AND S2.AREA_CD = 967
               AND
               (   S1.STATUS           = 'ACTIVE'
                OR S1.LAST_LOGIN_LOCTN = 'NEW YORK'
               )
               AND T.LOCATION IN ('NA' , 'EUR' , 'ASIA')
           )
    ;

  3. #3
    Join Date
    Jul 2013
    Posts
    24
    Example #1: cannot use because S1.SURR_KEY are not less than 9999.

    Example #2: Got following error message:
    SQL0811N* The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.
    *

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?

    Please post DDLs and INSERT statements to populate the tables by sample/test data,
    and the expected results from the sample/test data.

    Otherwise, our communication will be inefficient.
    Because, I need to ask you every my query examples(they might be including unnoticed simple mistakes/misunderstandings of me, or they might be inefficient.) without test them.
    That add extra dialologues which might be unnecessary if I could test the examples by myself.

    Removed Example 3 and 4, because of my serious misundersandings
    Last edited by tonkuma; 09-26-13 at 05:37.

  5. #5
    Join Date
    Jul 2013
    Posts
    24
    Thank you for taking time to posting response. here is the info.
    Unix DB2 version: DB2 v9.5.0.10.

    I tried Example#3 and then compared it with my UPDATE #1, #2, AND #3. It compared good with all except my update #1. Only T.C1 was not updated and left at default value 0 for T.C1 (see my update #3 statement), however T.C2 was updated correctly. When I run my update #1, it found the match for T.C1 and T.C2 I double checked column has a value in source table.

    For my update #3, solution now says we can set remaining to 1 instead of 9999 (SET T.C1 = 1,T.C2 = 1).


    #4 had error "SQL0206N "S1.STATUS" is not valid in the context where it is used."
    I have corrected reference to relevant table for Example#4 (must have been typo) before running in my environment. for instance, S.SURR_KEY is actually S1.SURR_KEY.


    Here is the DDL for all the tables involved:

    CREATE TABLE "SCHEMA.SOURCE1 S1" (
    "S1.SURR_KEY" INTEGER NOT NULL,
    "S1.CUSTNO" DECIMAL(10, 0) NOT NULL,
    "S1.SURR_KEY_2" INTEGER NOT NULL,
    "S1.STATUS" VARCHAR(20) NOT NULL,
    "S1.LAST_LOGIN_LOCTN" VARCHAR(20) NOT NULL
    )

    CREATE TABLE "SCHEMA.SOURCE2 S2" (
    "S2.SURR_KEY_2" INTEGER NOT NULL,
    "S2.AREA_CD" INTEGER NOT NULL,
    )

    CREATE TABLE "SCHEMA"."Table T" (
    "T.CUST_NO" DECIMAL(10, 0) NOT NULL,
    "T.LOCATION MEMB_STAT" VARCHAR(20) NOT NULL DEFAULT ,
    "T.C1" INTEGER NOT NULL DEFAULT ,
    "T.C2" INTEGER NOT NULL DEFAULT
    )

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry!

    I made serious mistake in Example 3 and 4(and also Example 1 and 2).

    I'll remove Example 3 and 4. and If possible, I'll post new/corrected examples.

  7. #7
    Join Date
    Jul 2013
    Posts
    24
    No problem and no sorry. It happen but this is very informative for me.
    thank you for all the big time help

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My mistake(which I realized) was that I forgot the case (in spite of your notice)
    where one row matched by S1.STATUS = 'ACTIVE' and another row matched by S1.LAST_LOGIN_LOCTN = 'NEW YORK' for a T.CUST_NO.



    How about the following corrected examples?

    Example 3a:
    Code:
    UPDATE SCHEMA.Table T
      SET  (c1 , c2)
         = (SELECT c1 , c2
             FROM  LATERAL
                   (SELECT COALESCE(
                              MAX(CASE S1.STATUS
                                  WHEN 'ACTIVE' THEN
                                       S1.SURR_KEY
                                  END )
                            , T.c1
                           ) AS c1
                         , COALESCE(
                              MAX(CASE S1.LAST_LOGIN_LOCTN
                                  WHEN 'NEW YORK' THEN
                                       S1.SURR_KEY
                                  END )
                            , T.c2
                           ) AS c2
                         , 0 AS c3
                     FROM  SCHEMA.SOURCE1 S1
                     INNER JOIN 
                           SCHEMA.SOURCE2 S2
                      ON   S2.SURR_KEY_2 = S1.SURR_KEY_2
                     WHERE S1.CUSTNO  = T.CUST_NO
                       AND S2.AREA_CD = 967
                       AND
                       (   S1.STATUS           = 'ACTIVE'
                        OR S1.LAST_LOGIN_LOCTN = 'NEW YORK'
                       )
                       AND T.LOCATION IN ('NA' , 'EUR' , 'ASIA')
                     GROUP BY
                           S1.CUSTNO
                    UNION ALL
                    SELECT 1 AS c1
                         , 1 AS c2
                         , 1 AS c3
                     FROM  sysibm.sysdummy1
                   ) AS S
             ORDER BY
                   c3
             FETCH FIRST 1 ROW ONLY
           )
    ;

    Example 4a:
    Code:
    UPDATE SCHEMA.Table T
      SET  (c1 , c2)
         = (SELECT COALESCE(s.c1 , f.c1)
                 , COALESCE(s.c2 , f.c2)
             FROM  (VALUES (1 , 1) ) AS f(c1 , c2)
             LEFT  OUTER JOIN
                   LATERAL
                   (SELECT COALESCE(
                              MAX(CASE S1.STATUS
                                  WHEN 'ACTIVE' THEN
                                       S1.SURR_KEY
                                  END )
                            , T.c1
                           ) AS c1
                         , COALESCE(
                              MAX(CASE S1.LAST_LOGIN_LOCTN
                                  WHEN 'NEW YORK' THEN
                                       S1.SURR_KEY
                                  END )
                            , T.c2
                           ) AS c2
                     FROM  SCHEMA.SOURCE1 S1
                     INNER JOIN 
                           SCHEMA.SOURCE2 S2
                      ON   S2.SURR_KEY_2 = S1.SURR_KEY_2
                     WHERE S1.CUSTNO  = T.CUST_NO
                       AND S2.AREA_CD = 967
                       AND
                       (   S1.STATUS           = 'ACTIVE'
                        OR S1.LAST_LOGIN_LOCTN = 'NEW YORK'
                       )
                       AND T.LOCATION IN ('NA' , 'EUR' , 'ASIA')
                     GROUP BY
                           S1.CUSTNO
                   ) AS s
              ON   0=0
           )
    ;

  9. #9
    Join Date
    Jul 2013
    Posts
    24
    Thank you very much, tried Example 3a, and it worked.

    if I wish to put condition on my-update-statement-#3 to only update T.C1 = 1,T.C2 = 1 where T.C1 = 0,T.C2 = 0 so it doesnt always update the ones that are already set to 1 last time how would i put this in Example 3a?

Posting Permissions

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