How to update first n Rows?

laknar · #1 (**permalink**) 04-21-09, 01:51

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

przytula_guy · #2 (**permalink**) 04-21-09, 06:00

create a stored procedure - count the records updated and stop when enough

stolze · #3 (**permalink**) 04-21-09, 08:39

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'

tonkuma · #4 (**permalink**) 04-21-09, 08:58

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

samibhashani · #5 (**permalink**) 03-27-12, 10:13

...................

Lenny77 · #6 (**permalink**) 03-27-12, 11:58

Quote:

Originally Posted by tonkuma

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

Lenny77 · #7 (**permalink**) 03-27-12, 12:55

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

Internet Services

Web Development

Digital Marketing

Consumer Tech