Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: Help to write update query

    Hi All,
    Please help me to write update query. Below are details.

    Table A:
    Records are like below...
    PK PROD LOC WEEK PRICE LASTW BASEP SID
    1 1 1 1 1 NULL 1.1 10
    2 1 1 2 2 NULL 1.1 10
    3 1 1 3 3 NULL 1.1 10
    4 2 2 1 1 NULL 2.1 20
    5 2 2 2 2 NULL 2.1 20
    6 2 2 3 3 NULL 2.1 20

    After update data records must be like below...

    PK PROD LOC WEEK PRICE LASTW BASEP SID
    1 1 1 1 1 1.1 1.1 10
    2 1 1 2 2 1 1.1 10
    3 1 1 3 3 2 1.1 10
    4 2 2 1 11 2.1 2.1 20
    5 2 2 2 21 11 2.1 20
    6 2 2 3 31 21 2.1 20

    For every set of PROD,LOC and SID,LASTW data must be updated with price of last week and for first week,LASTW data must be updated with BASEP data.

    I tried below query which is updating down to up and below is the output of the query

    update A A set A.LASTW=(select case when A.WEEK=1 then A.BASEP else B.PRICE END from A B where (A.SID=B.SID AND A.PROC=B.PROC AND A.LOC=B.LOC) and B.WEEK=A.WEEK+1;

    PK PROD LOC WEEK PRICE LASTW BASEP SID
    1 1 1 1 1 1.1 1.1 10
    2 1 1 2 2 3 1.1 10
    3 1 1 3 3 NULL 1.1 10
    4 2 2 1 11 2.1 2.1 20
    5 2 2 2 21 31 2.1 20
    6 2 2 3 31 NULL 2.1 20

    Thanks,
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... B.WEEK=A.WEEK+1;
    Isn't it "B.WEEK = A.WEEK - 1"?

  3. #3
    Join Date
    Mar 2008
    Posts
    136
    Thanks Ton!! But its not updating 1st week LASTW by BASEP.

    update A A set A.LASTW=(select case when A.WEEK=1 then A.BASEP else B.PRICE END from A B where (A.SID=B.SID AND A.PROC=B.PROC AND A.LOC=B.LOC) and B.WEEK=A.WEEK-1;

    PK PROD LOC WEEK PRICE LASTW BASEP SID
    1 1 1 1 1 NULL 1.1 10
    1 1 2 2 2 1 1.1 10
    1 1 3 3 3 2 1.1 10
    1 1 1 1 11 NULL 1.1 20
    1 1 2 2 21 11 1.1 20
    1 1 3 3 31 21 1.1 20
    Regards
    Pawan Kumar

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try...

    Code:
    UPDATE A t/*arget*/
       SET lastw
           = CASE
             WHEN t.week = 1 THEN
                  t.basep
             ELSE (SELECT r.price
                    FROM  A r/*eference*/
                    WHERE r.prod = t.prod
                      AND r.loc  = t.loc
                      AND r.sid  = t.sid
                      AND r.week = t.week - 1
                  )
            END
    ;

  5. #5
    Join Date
    Mar 2008
    Posts
    136
    Thanks a lot!

    There are 500 million records in the table and I have to update records. Is there any good approach to update the table?
    Regards
    Pawan Kumar

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are CREATE TABLE statement and CREATE INDEX statements?

    Do you want to update all of 500 million rows at once?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may be more effective than my last example.

    Example 2:
    Code:
    UPDATE (SELECT lastw
                 , LAG(price , 1 , basep)
                      OVER( PARTITION BY prod
                                       , loc
                                       , sid
                                ORDER BY week
                          ) AS lag_price
             FROM  A
           )
       SET lastw
         = lag_price
    ;

    And this index may be worth to try...
    Code:
    CREATE UNIQUE INDEX A__unique__prod_loc_sid_week
     ON A
        ( prod , loc , sid , week )
    ;
    Last edited by tonkuma; 09-10-12 at 12:52. Reason: Add UNIQUE to the INDEX.

  8. #8
    Join Date
    Mar 2008
    Posts
    136
    There are 35 columns in the table and almost all colums are double,integer..
    3 indexes in table and no index on A.LASTW

    I think open a cursor and in the cursor fetch 20K records for update untill end...

    in one go it will be a huge transaction that cant afford...
    Regards
    Pawan Kumar

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are 35 columns in the table and almost all colums are double,integer..
    3 indexes in table and no index on A.LASTW
    Why you skimped your work?
    Please copy and paste CREATE statements(or use db2look).


    I think open a cursor and in the cursor fetch 20K records for update untill end...
    Here is an example to issue COMMIT(s) for bulk delete.
    http://www.dbforums.com/db2/1682606-...mit-issue.html


    This is an example using the technique.

    Example 3: Issue COMMIT every nnnnn set of (prod , loc , sid).
    Code:
    BEGIN
    DECLARE /* update_cnt , last_pk , */
            last_drank INTEGER DEFAULT 0;
    
    REPEAT
       SELECT /* COUNT(*) , MAX(pk) , */
              MAX(drank)
        INTO  /* update_cnt , last_pk , */
              last_drank
        FROM  OLD TABLE
             (UPDATE (SELECT t.*
                           , LAG(price , 1 , basep)
                                OVER( PARTITION BY prod
                                                 , loc
                                                 , sid
                                          ORDER BY week
                                    ) AS lag_price
                           , DENSE_RANK()
                                OVER(     ORDER BY prod
                                                 , loc
                                                 , sid
                                    ) AS drank
                       FROM  A t
                       WHERE lastw IS NULL
                     )
                 SET lastw
                   = lag_price
               WHERE drank <= nnnnn
             )
       ;
    
       COMMIT;
    
       UNTIL last_drank < nnnnn
    END REPEAT;
    END@

  10. #10
    Join Date
    Mar 2008
    Posts
    136
    CREATE TABLE "MISC "."A" (
    "PK" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
    START WITH +1
    INCREMENT BY +1
    MINVALUE +1
    MAXVALUE +2147483647
    NO CYCLE
    CACHE 1000
    NO ORDER ) ,
    "PROD" INTEGER NOT NULL ,
    "LOC" INTEGER NOT NULL ,
    "SID" INTEGER NOT NULL ,
    "WEEK" SMALLINT NOT NULL ,
    "EPRICE" DOUBLE NOT NULL ,
    "BASEP" DOUBLE NOT NULL ,
    "PRICE" DOUBLE NOT NULL ,
    "PRERULE" DOUBLE NOT NULL ,
    "EVOL" DOUBLE NOT NULL ,
    "UVOL" DOUBLE NOT NULL ,
    "GMAR" DOUBLE NOT NULL ,
    "VCOS DOUBLE NOT NULL ,
    "UCOST" DOUBLE NOT NULL ,
    "REV" DOUBLE NOT NULL ,
    "IUNIT" DOUBLE NOT NULL ,
    "MCOST" DOUBLE NOT NULL ,
    "MCCOST" DOUBLE NOT NULL ,
    "SIUNIT" DOUBLE NOT NULL ,
    "EIUNIT" DOUBLE NOT NULL ,
    "SRICE" DOUBLE NOT NULL WITH DEFAULT 0 ,
    "VATRATE" DOUBLE ,
    "LINFO" SMALLINT ,
    "SHOUSE" DOUBLE ,
    "EHOUSE" DOUBLE ,
    "UHOUSE" DOUBLE ,
    "WSUPPLY" DECIMAL(7,3) ,
    "SIVALUE" DOUBLE ,
    "EIVALUE" DOUBLE ,
    "SHIVALUE" DOUBLE ,
    "EHIVALUE" DOUBLE ,
    "***ALUE" DOUBLE ,
    "SSVALUE" DOUBLE ,
    "EEVALUE" DOUBLE ,
    "LASTW" DOUBLE )
    IN "TBL32"
    ORGANIZE BY (
    ( "SID" ) )
    ;



    ALTER TABLE "MISC "."A"
    ADD CONSTRAINT "PK_A" PRIMARY KEY
    ("PK");




    CREATE UNIQUE INDEX "MISC "."UI_A" ON "MISC "."A"
    ("PROC" ASC,
    "LOC" ASC,
    "WEEK" ASC,
    "SID" ASC)
    ALLOW REVERSE SCANS;


    ALTER TABLE "MISC "."A"
    ADD CONSTRAINT "FK_A" FOREIGN KEY
    ("SID")
    REFERENCES "MISC "."S"
    ("SID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;


    We dont need to update all records..we added some filters....(after adding filter record count is around 250 million)


    update A A set A.LASTW=case when A.WEEK=1 then A.BASEP else (select B.PRICE from A B INNER JOIN SR on (B.SID=SR.SID) INNER JOIN S ON (S.SSID=SR.SSID) INNER JOIN P ON (S.PID=P.PID) WHERE S.TYPE = 2 AND S.STYPE = 2 AND A.SID=B.SID AND A.PROC=B.PROC AND A.LOC=B.LOC and A.WEEK=B.WEEK+1 AND CURRENT DATE BETWEEN P.SDATE AND P.EDATE) end;
    Regards
    Pawan Kumar

  11. #11
    Join Date
    Nov 2011
    Posts
    334
    if no rows satisfied the contidion :
    Code:
    SELECT B.PRICE
    FROM   A B
           INNER JOIN SR
             ON (B.SID = SR.SID)
           INNER JOIN S
             ON (S.SSID = SR.SSID)
           INNER JOIN P
             ON (S.PID = P.PID)
    WHERE  S.TYPE = 2
           AND S.STYPE = 2
           AND A.SID = B.SID
           AND A.PROC = B.PROC
           AND A.LOC = B.LOC
           AND A.WEEK = B.WEEK + 1
           AND CURRENT DATE BETWEEN P.SDATE AND P.EDATE
    Then the a.lastw will still be the null,is that what you expected ?

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there was no WHERE clause in your UPDATE statement,
    all rows will be updated even if A.LASTW was set to NULL.

    This might be an example of WHERE clause to filter out some rows from subject of update.
    Code:
    update A A
       set A.LASTW
         = case
           when A.WEEK = 1 then
                A.BASEP
           else (select B.PRICE
                  from  A B
                  INNER JOIN
                        SR
                   on   SR.SID  = B .SID
                  INNER JOIN
                        S
                   ON   S .SSID = SR.SSID
                  INNER JOIN
                        P
                   ON   P .PID  = S .PID
                  WHERE S.TYPE  = 2
                    AND S.STYPE = 2
                    AND B.SID   = A.SID
                    AND B.PROC  = A.PROC
                    AND B.LOC   = A.LOC
                    and B.WEEK  = A.WEEK - 1
                    AND CURRENT DATE
                        BETWEEN P.SDATE AND P.EDATE
                )
           end
     WHERE EXISTS
           (select 0
             FROM  SR
              on   SR.SID  = A .SID
             INNER JOIN
                   S
              ON   S .SSID = SR.SSID
             INNER JOIN
                   P
              ON   P .PID  = S .PID
             WHERE S.TYPE  = 2
               AND S.STYPE = 2
               AND CURRENT DATE
                   BETWEEN P.SDATE AND P.EDATE
           )
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a modification of Example 3 to be more close to your DDLs and last UPDATE statement.
    For example, a dimension key SID was moved to the first column of ORDER BY and PARTITION BY.

    Example 4: Issue COMMIT every nnnnn set of filtered (sid , prod , loc).
    Code:
    BEGIN
    DECLARE /* update_cnt , */
            last_dns_rank
          , last_sid /* , last_prod , last_loc */
            INTEGER DEFAULT 0;
    
    REPEAT
       SELECT /* COUNT(*) , */
              MAX(dns_rank)
           ,  MAX(sid)
        INTO  /* update_cnt , */
              last_dns_rank
            , last_sid
        FROM  OLD TABLE
             (UPDATE (SELECT A.*
                           , LAG(A.price , 1 , A.basep)
                                OVER( PARTITION BY A.sid , A.prod , A.loc
                                          ORDER BY A.week
                                    ) AS lag_price
                           , DENSE_RANK()
                                OVER(     ORDER BY A.sid , A.prod , A.loc
                                    ) AS dns_rank
                       FROM  A A
                       INNER JOIN
                             SR
                        ON   SR.sid  = A .sid
                       INNER JOIN
                             S
                        ON   S .ssid = SR.ssid
                       INNER JOIN
                             P
                        ON   P .pid  = S .pid
                       WHERE S.type  = 2
                         AND S.stype = 2
                         AND CURRENT DATE
                             BETWEEN P.sdate AND P.edate
                         AND A.lastw IS NULL
                         AND A.sid  >= last_sid
                     )
                 SET lastw
                   = lag_price
               WHERE dns_rank <= nnnnn
             )
       ;
    
       COMMIT;
    
      UNTIL last_dns_rank < nnnnn
    END REPEAT;
    END@

    The following index might be effective for this update statement.
    Code:
    CREATE UNIQUE INDEX MISC.UI_B
     ON MISC.A
     ( SID ASC , PROD ASC , LOC ASC , WEEK ASC )
    /* ALLOW REVERSE SCANS */
    ;

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE UNIQUE INDEX "MISC "."UI_A" ON "MISC "."A"
    ("PROC" ASC,
    "LOC" ASC,
    "WEEK" ASC,
    "SID" ASC)
    ALLOW REVERSE SCANS;
    update A A set A.LASTW=case when A.WEEK=1 then A.BASEP else (select B.PRICE from A B INNER JOIN SR on (B.SID=SR.SID) INNER JOIN S ON (S.SSID=SR.SSID) INNER JOIN P ON (S.PID=P.PID) WHERE S.TYPE = 2 AND S.STYPE = 2 AND A.SID=B.SID AND A.PROC=B.PROC AND A.LOC=B.LOC and A.WEEK=B.WEEK+1 AND CURRENT DATE BETWEEN P.SDATE AND P.EDATE) end;
    Where is "PROC" column in "MISC "."A" table?

  15. #15
    Join Date
    Mar 2008
    Posts
    136
    sorry that's PROD not PROC
    Regards
    Pawan Kumar

Posting Permissions

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