Select , Update , Delete N number of rows in db2

For Select : In db2 , fetching a limited number of rows is very simple. You can use  FETCH FIRST n ROWS ONLY with select query. In some applications, a select query with certain condtion or without condition may return a large number of rows, but you may need only a small subset of those rows.  For example , in a web page with ajax support , you may search a city that starts with ' N'  which  returns many numbers of rows and are filled in a combo.  Retrieving the entire result  from the table can be inefficient and also filling all the records in a combo may lead to poor response of the browser in a  client running with low memory.  So it is better to limit the result with first n number of records. Becaue it improves the performance of queries with  large result tables when you need only a limited number of rows. Also suitable for the system with less memory.   This can be done in db2 easily with  FETCH FIRST n ROWS ONLY clause  in a SELECT statement.  FETCH FIRST n ROWS ONLY, DB2 prefetches only the first  n rows.  For example , to fetch first 10 cities  that start with 'N' , the below query is used. 

                    Select cityCode , cityName from city where cityName like 'N%'  fetch first 10 rows only.

For Update :   In many applications, an update  query with  condtion or without condition may update a large number of rows  ,     but you may need to update only a small subset of those rows 

Consider the following scenerios

        1.Many viewers may send  the  right answer to the question asked in a TV programme, but they may need to update  prize amount for  the first 100 rows in the table.  Then how to  update the first 100 Rows in a table?   There is no direct query in db2 to update the first n number of records.   You can use update command with select query using FETCH first 100 ROWs ONLY.  The update query  is as follows. 

            UPDATE ( SELECT prize_amount FROM fs1  where right_answer='c'  FETCH first 100 ROWs ONLY ) SET prize_amount = 1000;

2. Suppose you are replicating  new rows or modified rows  from source table of  a db to target table in a remote db based on a condition(for eg. where replication_done='N')  through java code. One way to do is to export all the rows that matches the condition (replication_done='N')  to a text file using java code. Then the records in the text file may be imported to the table in the remote database through code. Some times (Ist time) you may need to replicate the whole table  having huge number of records (for e.g. 100000 records).  It is inefficient to replicate all records at one go. Even the import operation may  fail as the  size of the exported file becomes  too large or exceeds the limit. In this situation you can export the rows part by part (50000 + 50000 rows )  and then do the import. ( i.e. updating the first 50000 rows  with replication_done='N'   then do the export & import and updating the second  50000 rows  with replication_done='N'  then do the export & import ).   

For updating the first 50000 rows among 100000 records with replication_done = 'N' ,  use the primary key and rownumber() as given below

 UPDATE  sales  SET replication_done = 'N'   WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales order by sale_trn_id ) AS R   WHERE rn <=50000)

           sale_trn_id is the primary key. 

For updating next 50000 rows with replication_done = 'N' ,  use the below update query

UPDATE  sales  SET replication_done = 'N'   WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales order by sale_trn_id ) AS R   WHERE rn >50000)

Some other useful update queries for updating first n records using unique column OR  unique combination of  the columns of the table 

Using unique column:

       Update sales SET replication_done = 'N'  where sale_trn_id  in (select sale_trn_id  from sales   order by sale_trn_id   fetch first 50000 rows only) 

Using  unique combination of  the columns of the table:

       Update tbl_name SET expression  Where (col1, col2,..., coln) in  (select col1, col2,. coln  from tbl_name   order by col1, col2,. coln   fetch first n rows only)  

       eg.  Update salesmaster SET price = price*0.10  where ( item_code, cat_code)  in (select  item_code,cat_code  from salesmaster   order by  item_code, cat_code   fetch first 100 rows only) 

For Deletion :

     To delete the rows whose rownumber is greater than or equal to 50000 using unique key and rownumber()

          delete from  sales    WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales ) AS R   WHERE rn >=50000 ) 

     To delete the first 50000 rows in a table using the unique id

         delete from sales where sale_trn_id  IN (select sale_trn_id   from sales  order by sale_trn_id   fetch first 50000 rows only)