602SQL Documentation Index  
<­FETCH Statement UpCursors in SQL DELETE CURRENT OF Statement­>

UPDATE CURRENT OF Statement

statement_UPDATE_CURRENT_OF ::= UPDATE [ table ] SET assignment {, assignment }…  WHERE 
    CURRENT OF cursor_name;

The UPDATE CURRENT OF statement (or Positioned UPDATE) makes the changes specified in the assignments in the row a cursor specified by the cursor_name is set on. The cursor_name must either be declared in the cursor declaration, in the FOR statement or has to designate a query belonging to the application and stored in the database. Both the cursor as a whole and the columns on the left side of all assignments must be editable.

If a table is specified in the statement, it must be the only table accessible by the specified cursor.

If the specified cursor is not open, the error sqlstate 34000 (SQ_INVALID_CURSOR_NAME) occurs. If the cursor is not set on a row (e.g. it is positioned before the first or after the last row), the error sqlstate 24000 (SQ_INVALID_CURSOR_STATE) occurs. If the statement is executed in a READ ONLY transaction, the error sqlstate 25006 (SQ_TRANS_STATE_RDONLY) occurs.


Usage Example:

Change the prices from the price list in the following way: lower the prices of list items beginning with X by 10%, raise the prices for items beginning with A by 100%, and delete the rest:

PROCEDURE PriceListChange();
BEGIN 
  DECLARE err_notfound BIT DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN SET err_notfound=TRUE; END;
  DECLARE curprice SENSITIVE CURSOR FOR 
    SELECT item_number, price
    FROM PriceList
  FOR UPDATE; 
  DECLARE Item CHAR(20); 
  DECLARE price NUMERIC(14,2); 
  OPEN curprice; 
  LabelLoop: LOOP 
     FETCH NEXT FROM curprice INTO Item, Price; 
     IF err_notfound IS TRUE THEN LEAVE LabelLoop; END IF; 
     IF SUBSTRING(Item FROM 1 FOR 1) = 'X' THEN 
       UPDATE SET Price = Price*0.9 WHERE CURRENT OF curprice;
     ELSEIF SUBSTRING(Item FROM 1 FOR 1) = 'A' THEN 
       UPDATE SET Price = Price*2 WHERE CURRENT OF curprice;
     ELSE
       DELETE WHERE CURRENT OF curprice;
     END IF; 
  END LOOP LabelLoop; 
  CLOSE curprice; 
END;

See

<­FETCH Statement UpCursors in SQL DELETE CURRENT OF Statement­>