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 

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. Use the SQLGetCursorName() API to obtain the cursor name of the active result set.

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