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