• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Updating Through an SQL Cursor

    June 23, 2004 Hey, Ted

    Thanks for the many SQL tips you and others provide in Four Hundred Guru. The more I learn about SQL, the more I like it. Maybe you can explain something I have yet to figure out. I have learned how to change a set of records in one fell swoop with the UPDATE command. What I have not learned how to do is to update a single record that was read through a cursor.

    –Matt

    SQL provides a special phrase, CURRENT OF, which you place in the WHERE clause. The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. IBM calls this type of update a positioned update. The set-at-a-time update to which you referred is called a searched update. Searched updates give you a lot of bang for the buck, but positioned updates are needed when the update logic is complex.

    Here’s a simple illustration using the RPG language. First let’s look at a program that uses traditional I/O methods to grant 4 percent salary increases to employees with an ATYPE code of W.

    FEmployee  uf   e             disk
    
    C                   dow       '1'
    C
    C                   read      EmplRec
    C                   if        %eof
    C                   leave
    C                   endif
    C
    C                   if        AType = 'W'
    C                   eval      Salary *= 1.04
    C                   update    EmplRec
    C                   endif
    C
    C                   enddo
    C
    C                   eval      *inlr = *on
    

    This program could be replaced with one searched UPDATE command, but I want an easily understood example, so here’s the equivalent using a positioned update.

    D EmplRec       e ds                  extname(Employee)
    
    C/exec sql
    C+    set option commit=*none,closqlcsr=*endmod
    C/end-exec
    C/exec sql
    C+    declare EmpCursor cursor for
    C+       select * from Employee
    C+          for update of Salary
    C/end-exec
    C/exec sql
    C+    open EmpCursor
    C/end-exec
    C                   dow       '1'
    C
    C/exec sql
    C+    fetch EmpCursor into :EmplRec
    C/end-exec
    C                   if        SqlStt <> '00000'
    C                   leave
    C                   endif
    C
    C                   if        AType = 'W'
    C                   eval      Salary *= 1.04
    C/exec sql
    C+    update Employee set Salary = :Salary
    C+       where current of EmpCursor
    C/end-exec
    C                   endif
    C
    C                   enddo
    C/exec sql
    C+    close EmpCursor
    C/end-exec
    C
    C                   eval      *inlr = *on
    

    I want you to notice two things. First, the field to be changed has been listed in the cursor declaration. If you want to update more than one field, list the fields and separate them with commas.

    Second, I’ve included the CURRENT OF phrase in the UPDATE command. CURRENT OF is also permitted on the DELETE command.

    These concepts are the same for more complicated programs, where more complex conditions determine whether a record is updated or not.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    ProData Now Supports Database Utility on Linux The eServer i5 Versus Unix Servers

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle