Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Multiple SQL UPDATEs against a single cursor fetch?
has no ratings.
Published: 24 Jan 2014
Revised: 24 Jan 2014 - 3349 days ago
Last viewed on: 27 Mar 2023 (4186 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.

Multiple SQL UPDATEs against a single cursor fetch? Published by: Chris Proctor on 24 Jan 2014 view comments(5)

Good morning. Question, if I do a FETCH on a SELECT statement that has "FOR UPDATE OF" specified, can I do multiple updates to that record before FETCHING the next record? What I want to do is do an UPDATE to a "start date/time" field with the "WHERE CURRENT OF <cursor name>", then when the processing for that record is finished, do another UPDATE to the "end date/time" field using the "WHERE CURRENT OF <cursor name>" again. Once I've updated both start and end date and times, I would then FETCH the next record.

Let me know if that's ok. Thanks!

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

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Multiple SQL UPDATEs against a single cursor fetch?
Posted: 9 years 2 months 2 days 19 hours 1 minutes ago

Uh I'll take a guess. YES. When you do an SQL Cursor UPDATE, the row is still locked (amazingly, it's true). That tells me you could UPDATE again if you wanted to. Try it and let us know. Thanks!

Chris Ringer

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Multiple SQL UPDATEs against a single cursor fetch?
Posted: 9 years 2 months 2 days 18 hours 32 minutes ago

I'll do that! Thanks, Chris. I just wasn't sure if the update would release it and cause an error like it would if you did a CHAIN and UPDATE to a record. Thanks again!

Posted by: Ringer
Premium member *
Comment on: Multiple SQL UPDATEs against a single cursor fetch?
Posted: 9 years 2 months 2 days 18 hours 1 minutes ago
Edited: Fri, 24 Jan, 2014 at 12:01:49 (3349 days ago)

http://www.midrangenews.com/view?id=2140

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Multiple SQL UPDATEs against a single cursor fetch?
Posted: 9 years 2 months 2 days 17 hours 58 minutes ago


Thanks, Chris. It did work!! Awesome!! Thanks!

Posted by: Ringer
Premium member *
Comment on: Multiple SQL UPDATEs against a single cursor fetch?
Posted: 9 years 2 months 2 days 16 hours 9 minutes ago

Coool. Thanks for letting us know. 

Chris Ringer