Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: How to update first n Rows?

    How to update first n Rows?

    Update TABLE1 SET FLAG='R' WHERE FLAG='N';

    FLAG 'N' Values have thousands of records.

    i want to update only first 15 rows.

    if 15 records contains 'R' i should not update as 'R'

    If FLAG 'R' is less than 15 records then update as 'R'(if 7 records already contains 'R" then i need Update only 8 records as 'R')
    Last edited by laknar; 04-21-09 at 02:55.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    create a stored procedure - count the records updated and stop when enough
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What are the first 15 rows for you? Any row?

    You could also do that (untested):
    Code:
    UPDATE ( SELECT * FROM table1 WHERE flag = 'N' FETCH FIRST 15 ROWS ONLY )
    SET flag = 'R'
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I added "AS R" to make the statement work on DB2 V9.1 or earlier.

    Change <primary key> to the name of the primary key(or a unique column) of your TABLE1.

    Code:
    UPDATE
           TABLE1
       SET flag = 'R'
     WHERE <primary key> IN
         ( SELECT <primary key>
             FROM
                ( SELECT <primary key>
                       , ROWNUMBER() OVER() rn
                    FROM TABLE1
                   WHERE flag = 'N'
                ) AS R
            WHERE rn <= 15
                      - (SELECT COUNT(*)
                           FROM TABLE1
                          WHERE flag = 'R')
         )
    ;
    Last edited by tonkuma; 04-21-09 at 10:15.

  5. #5
    Join Date
    Mar 2012
    Posts
    1
    ...................

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by tonkuma View Post
    I added "AS R" to make the statement work on DB2 V9.1 or earlier.

    Change <primary key> to the name of the primary key(or a unique column) of your TABLE1.

    Code:
    UPDATE
           TABLE1
       SET flag = 'R'
     WHERE <primary key> IN
         ( SELECT <primary key>
             FROM
                ( SELECT <primary key>
                       , ROWNUMBER() OVER() rn
                    FROM TABLE1
                   WHERE flag = 'N'
                ) AS R
            WHERE rn <= 15
                      - (SELECT COUNT(*)
                           FROM TABLE1
                          WHERE flag = 'R')
         )
    ;
    Tonkuma, I believe the number:

    Code:
    15 - (SELECT COUNT(*)
              FROM TABLE1
             WHERE flag = 'R')
    Could be negative because of COUNT(*) you'll get on whole table.

    laknar, how somebody can understand it was updated 'R' to 'R', or not.

    Maybe somebody use TRIGGER ?


    Lenny

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Tested Solution

    If you have UNIQUE_ID on the table you can run the query:

    Code:
    Update TABLE1 SET FLAG = 'R' 
    Where unique_id in 
    (select unique_id from TABLE1 
      order by 1
      fetch first 15 rows only)
    And FLAG = 'N' 
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    If not, you can use the unique combination the columns of the TABLE1:

    Code:
    Update TABLE1 SET FLAG = 'R' 
    Where (clmni1, clmni2,..., clmnik) in 
    (select clmni1, clmni2,..., clmnik from TABLE1 
      order by 1, 2, ..., k
      fetch first 15 rows only)
    And FLAG = 'N' 
    ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
    This code has to work.
    I have tried it on my test table with perfect result.

    Lenny

Posting Permissions

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