WHERE CURRENT OF

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

Format 1

EXEC SQL     UPDATE table_name   SET set_clause   WHERE CURRENT OF Cursor_Name   END-EXEC

Format 2

EXEC SQL     DELETE FROM table_name   WHERE CURRENT OF Cursor_Name   END-EXEC

 

Syntax rules

1.	WHERE CURRENT OF can be used only within UPDATE or DELETE statements.

General rules

1.	Cursor-Name must be previously defined by a DECLARE statement.

Examples

Declare, open and fetch a cursor. Delete records where the field cust-name is empty.

exec sql     declare cust_cur  cursor for select * from customers end-exec                  exec sql     open cust_cur end-exec        perform until 1 = 2    exec        sql fetch next cust_cur into :ws-cust-code, :ws-cust-name    end-exec      if ws-cust-name = space       exec sql             delete from customers where current of cust_cur       end-exec    end-if      if sqlcode = 100       exit perform    end-if   end-perform   exec sql     close cust_cur end-exec