• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    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

  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11
  • You Ought To Be Committed
  • Thoroughly Modern: What You Need to Know About IBM i Security
  • Spring Brings Events To IBM i Community, And More
  • As I See It: AI-AI-O
  • IBM i PTF Guide, Volume 25, Number 10

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 © 2023 IT Jungle