UPDATE (positioned)
The positioned UPDATE statement updates the current row of a rowset associated with an 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. Refer to 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
CREATE PROCEDURE
CREATE TRIGGER