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 - 3743 days ago
Last viewed on: 23 Apr 2024 (4516 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: 10 years 3 months 1 days 8 hours 22 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: 10 years 3 months 1 days 7 hours 54 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: 10 years 3 months 1 days 7 hours 23 minutes ago
Edited: Fri, 24 Jan, 2014 at 12:01:49 (3743 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: 10 years 3 months 1 days 7 hours 20 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: 10 years 3 months 1 days 5 hours 30 minutes ago

Coool. Thanks for letting us know. 

Chris Ringer