Midrange News for the IBM i Community

Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Using "where current of" in an SQL Update
has no ratings.
Published: 06 Nov 2013
Revised: 07 Nov 2013 - 3681 days ago
Last viewed on: 05 Dec 2023 (5036 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.

Using "where current of" in an SQL Update Published by: Chris Proctor on 06 Nov 2013 view comments(3)

Good morning all. I'm having an issue and I can't figure out what the problem might be. I have a program that is passed a file and field name and I a dynamically creating the SELECT and the UPDATE statements, but I believe there might be an issue. I just want to verify with you that I have these set up correctly.

First of all, I build my SELECT statement based on the parms passed to the program:

// format SQL statements for file to be updated  
sltstmt = 'select ' + %trim(pfldnm) + ' from ' + %trim(plibnm) + '/' +
%trim(pfilnm) + ' for update of ' + %trim(pfldnm) +           ' with nc';

// prepare, declare the cursor 
exec sql prepare styleCursor from :sltstmt;
exec sql declare c1 cursor for styleCursor;
exec sql open c1;
     dow sqlcode = 0;
       exec sql fetch next from c1 into :stylno;
       if sqlcode = 0;
         // retrieve material number
        // update record
          updstmt = 'update ' + %trim(plibnm) + '/' + %trim(pfilnm) + 
   ' set ' + %trim(pfldnm) + ' = ''' + 
                          %trim(matno)   ''' where current of c1';
           exec sql execute immediate :updstmt;                        

First of all, I'd like to confirm that "fetch next" will only retrieve one record. Next, I want to make sure that the "where current of will only update the record that was retrieved by the c1 cursor.

Any comments would be greatly appreciated.


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


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: Using "where current of" in an SQL Update
Posted: 10 years 29 days 20 hours 47 minutes ago

Yes, assuming the DOW loop has a closing ENDDO statement... the loop will read each row returned by the open cursor, one at a time, and then your UPDATE where current of cursor would update that individual row.

Interesting idea to use execute immediate for the UPDATE operation--never thought or considered doing that before.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Using "where current of" in an SQL Update
Posted: 10 years 29 days 20 hours 31 minutes ago

Thanks for the confirmation, Bob!! The execute immediate seems to work well. My problem was, the file that I'm retrieving the new style from to update our master file had dups. Ooops!! Lol. It cause quite a mess and now I have to figure out how to fix it. I guess they call this job security, but I call it pain the a**!! Lol

Thanks again!

Posted by: Ringer
Premium member *
Comment on: Using "where current of" in an SQL Update
Posted: 10 years 28 days 17 hours 33 minutes ago

This *probably* won't affect your code but just an FYI, when you use UPDATE WHERE CURRENT OF it does update the row, but also *keeps* the row locked. Yes, it's weird. You have to fetch another row or close the cursor, exit with *INLR=*ON, etc to release the record lock. 

Chris Ringer