Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: doing UPDATE with the result of a recursive CTE query

    Hello all !!!

    I'me using DB2 v9 for Z/OS

    I have the following recursive query
    WITH SEL1 (c1, c2, c3) as (select a1, b1, c1 from t1 where...),
    SEL2 (x1, x2, x3) as (select a2, b2, c2 from t1 where...)
    SELECT * FROM SEL1, SEL2 where ... );

    I'd like to run a UPDATE command using the result of the recursive query... Is that possible

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Note: your sample looks not recursive CTE. recursive CTE includes UNION ALL.


    data-change-table-reference might be a solution.

    Hore is an example of UPDATE using common-table-expressions(including non-recursive CTE and recursive CTE).
    Although it was tested on DB2 9.7 for Windows,
    I thought that it would work on DB2 9.1 for z/OS.


    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE points
       SET prev_points = NULL
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points
    ;
    ------------------------------------------------------------------------------
    SELECT * FROM points
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           -
              2          10          18           -
              3          10          20           -
              4          20           5           -
              5          20          15           -
              6          20          20           -
              7          20          18           -
              8          20           7           -
              2          30          20           -
              4          30          10           -
              5          30           7           -
              7          30          18           -
    
      12 record(s) selected.
    SELECT statement(doing UPDATE by using data-change-table-reference) with CTE:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      numbered_points AS (
    SELECT p.*
         , ROW_NUMBER()
              OVER( PARTITION BY userid
                        ORDER BY id
                  ) AS row_num
     FROM  points p
    )
    , rcte(id , userid , row_num , prev_points) AS (
    SELECT id , userid , 1 , 0
     FROM  numbered_points
     WHERE row_num = 1
    UNION ALL
    SELECT n.id
         , r.userid
         , r.row_num + 1
         , p.points
     FROM  rcte            r
         , numbered_points n
         , numbered_points p
     WHERE r.row_num < 1000
       AND n.userid  = r.userid
       AND n.row_num = r.row_num + 1
       AND p.userid  = r.userid
       AND p.row_num = r.row_num
    )
    SELECT COUNT(*) AS updated_rows
     FROM  FINAL TABLE
          (UPDATE points AS t
              SET prev_points
               = (SELECT prev_points
                   FROM  rcte s
                   WHERE s.id      = t.id
                     AND s.userid  = t.userid
                 )
          )
    ;
    ------------------------------------------------------------------------------
    
    UPDATED_ROWS
    ------------
              12
    
      1 record(s) selected.
    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points;
    ------------------------------------------------------------------------------
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           0
              2          10          18          15
              3          10          20          18
              4          20           5           0
              5          20          15           5
              6          20          20          15
              7          20          18          20
              8          20           7          18
              2          30          20           0
              4          30          10          20
              5          30           7          10
              7          30          18           7
    
      12 record(s) selected.

    Sample table is same as in this thread.
    Quote Originally Posted by tonkuma View Post
    ...
    ...

    Test data: Add more samples.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE points
    ( id          INT NOT NULL
    , userid      INT NOT NULL
    , points      INT NOT NULL
    , prev_points INT
    , PRIMARY KEY (id , userid)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO points
    (id , userid , points)
    VALUES
      (  1 , 10 , 15 )
    , (  2 , 10 , 18 )
    , (  3 , 10 , 20 )
    , (  4 , 20 , 05 )
    , (  5 , 20 , 15 )
    , (  6 , 20 , 20 )
    , (  7 , 20 , 18 )
    , (  8 , 20 , 07 )
    , (  2 , 30 , 20 )
    , (  4 , 30 , 10 )
    , (  5 , 30 , 07 )
    , (  7 , 30 , 18 )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM points;
    ------------------------------------------------------------------------------
    
    ID          USERID      POINTS      PREV_POINTS
    ----------- ----------- ----------- -----------
              1          10          15           -
              2          10          18           -
              3          10          20           -
              4          20           5           -
              5          20          15           -
              6          20          20           -
              7          20          18           -
              8          20           7           -
              2          30          20           -
              4          30          10           -
              5          30           7           -
              7          30          18           -
    
      12 record(s) selected.
    Last edited by tonkuma; 06-07-12 at 08:28.

  3. #3
    Join Date
    May 2009
    Posts
    17
    Thanks for your reply....
    You are right my query is not recursive CTE.

    I tried to run your query on my environement but it doesn't work. I guess that it's due to the fact that we are en z/OS

    thanks !
    rd4004

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... it doesn't work. ...
    I annoyed to hear/read this kind of phrases.

    Did you received some error message(s)?
    If so, PLEASE publish(copy & paste) the message text(s) and code(s).

    If you got unexpected results, PLEASE publish your test data, received result(s), and your expected result(s).

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although it was tested on DB2 9.7 for Windows,
    I thought that it would work on DB2 9.1 for z/OS.
    I thought that, because of seeing manuals.
    ROW_NUMBER and data-change-table-reference would be newley supported from DB2 9.1 for z/OS.

    For example,
    DB2 9 - DB2 SQL - OLAP specification

    DB2 9 - DB2 SQL - table-reference

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed.
    Because of duplicated posts.

Tags for this Thread

Posting Permissions

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