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 - 3845 days ago
Last viewed on: 16 May 2024 (5208 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 6 months 13 days 2 hours 56 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 6 months 13 days 2 hours 40 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 6 months 11 days 23 hours 43 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