• 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
    ProData

    Simple.  Reliable.  Powerful. 

    IBM i software to help you navigate your universe!

    Use these tools to get where you are going:

    DBU to easily, yet securely, access the data on your IBM i or remote system.
    RDB Connect for powerful record level access to remote data using RPG, CL or COBOL.

    SQL/Pro and Audit offer reliable SQL data processing, tracking and reporting.

    RDR retrieves those records which were deleted in error.

    And more!

    Join our DBUniverse of loyal, happy customers and experience your destination…

    Do IT with DBU!

    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

Volume 4, Number 21 -- June 23, 2004
THIS ISSUE
SPONSORED BY:

Advanced Systems Concepts
iTera
WorksRight Software
Bug Busters Software Engineering

Table of Contents

  • IBM Enhances iSeries Access Family with Lots of Goodies
  • Printing Barcodes Without an IPDS Printer
  • Updating Through an SQL Cursor
  • Admin Alert: Changing the Limits on OS/400 Job Spool Files

Content archive

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

Recent Posts

  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM
  • Critical Security Vulnerability In PowerVM Hypervisor
  • IBM Power: Hosted On-Premises Or In The Cloud?
  • Guru: Watch Out For This Pitfall When Working With Integer Columns
  • As I See It: Bob-the-Bot
  • IBM i PTF Guide, Volume 25, Number 21

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