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 - 3598 days ago
Last viewed on: 30 Nov 2023 (4374 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 10 months 6 days 14 hours 19 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 10 months 6 days 13 hours 50 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 10 months 6 days 13 hours 20 minutes ago
Edited: Fri, 24 Jan, 2014 at 12:01:49 (3598 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 10 months 6 days 13 hours 16 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 10 months 6 days 11 hours 27 minutes ago

Coool. Thanks for letting us know. 

Chris Ringer