UPDATE (positioned)
The positioned UPDATE statement updates the current row of a rowset associated with a SQL cursor.
Syntax
UPDATE [ table-name ] SET column-name = proc-expr [ , column-name = proc-expr ]...
WHERE CURRENT OF cursor-name
table-name ::= user-defined-name
cursor-name ::= user-defined-name
Remarks
This statement is allowed in stored procedures, triggers, and at the session level.
Note Even though positioned UPDATE is allowed at the session level, the DECLARE CURSOR statement is not. The method to obtain the cursor name of the active result set depends on the PSQL access method your application uses. See the PSQL documentation for that access method.
The table-name may be specified in the positioned UPDATE statement only when used at the session level. Table-name cannot be specified with a stored procedure or trigger.
Examples
The following sequence of statements provide the setting for the positioned UPDATE statement. The required statements for a positioned UPDATE are DECLARE CURSOR, OPEN CURSOR, and FETCH FROM cursorname.
The positioned UPDATE statement in this example updates the name of the course HIS 305 to HIS 306.
CREATE PROCEDURE UpdateClass();
BEGIN
DECLARE :CourseName CHAR(7);
DECLARE :OldName CHAR(7);
DECLARE c1 cursor FOR SELECT name FROM course WHERE name = :CourseName FOR UPDATE;
SET :CourseName = 'HIS 305';
OPEN c1;
FETCH NEXT FROM c1 INTO :OldName;
UPDATE SET name = 'HIS 306' WHERE CURRENT OF c1;
END;
See Also