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 - 1363 days ago
Last viewed on: 18 Oct 2017 (2458 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: 3 years 8 months 24 days 34 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: 3 years 8 months 24 days 5 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: 3 years 8 months 23 days 23 hours 35 minutes ago
Edited: Fri, 24 Jan, 2014 at 12:01:49 (1363 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: 3 years 8 months 23 days 23 hours 31 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: 3 years 8 months 23 days 21 hours 42 minutes ago

Coool. Thanks for letting us know. 

Chris Ringer