Midrange News for the IBM i Community

Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
UPDATE number of rows in a stored procedure?
has no ratings.
Published: 27 Sep 2011
Revised: 23 Jan 2013 - 4051 days ago
Last viewed on: 22 Feb 2024 (5153 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

UPDATE number of rows in a stored procedure? Published by: Chris Proctor on 27 Sep 2011 view comments(1)

In a stored procedure I need to update 100 rows in a file with a batch number if the batch number is currently 0. It doesn't look like having "fetch 100 rows only" is valid on an UPDATE statement, so is there another way that I can accomplish the same thing?

Here's what my statement currently looks like:

UPDATE cscedipoh              
SET    ehbtch = v_batch_number
where  ehbtch = 0             
FETCH  first 100 rows only;   

Any suggestions would be greatly appreciated!

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: UPDATE number of rows in a stored procedure?
Posted: 12 years 5 months 16 hours 45 minutes ago

Just off the cuff, sounds like you need to use a cursor in a loop and update where current of the cursor. Either the cursor has the "for 100 rows," or use a variable that you increment as you do the fetches, and leave the loop after the 100th iteration. Either way you'll need to test for no row retrieved by the fetch and quit the loop (i.e., end-of-file).