602SQL Documentation Index  
<­UPDATE CURRENT OF Statement UpCursors in SQL CLOSE Statement­>

DELETE CURRENT OF Statement 

statement_DELETE_CURRENT_OF ::= DELETE [ FROM table ] WHERE CURRENT OF cursor_name;

The DELETE CURRENT OF statement (or Positioned DELETE) deletes a row that is set by the position of a cursor of the specified name. The cursor_name must either be declared in a cursor declaration, in the FOR statement, or must designate a query that belongs to the application and stored in the database. The cursor must allow record deletion.

If a table is specified in the statement, it must be the only table accessible by the 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 record or after the last record), the error sqlstate 24000 (SQ_INVALID_CURSOR_STATE) occurs. If this statement is executed in a READ ONLY transaction, the error sqlstate 25006 (SQ_TRANS_STATE_RDONLY) occurs.


Example:

Change the prices in the price list by lowering the prices of items beginning with X by 10%, raise the prices for items beginning with A by 100%, and delete the rest.

PROCEDURE PriceChange();
BEGIN 
  DECLARE err_notfound BIT DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 
    BEGIN SET err_notfound=TRUE; END;
  DECLARE defaultvalue SENSITIVE CURSOR FOR 
    SELECT item_number, price
    FROM itemlist
  FOR UPDATE; 
  DECLARE Item CHAR(20); 
  DECLARE price NUMERIC(14,2); 
  OPEN defaultvalue; 
  LabelLoop: LOOP 
     FETCH NEXT FROM defaultvalue 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 defaultvalue;
     ELSEIF SUBSTRING(Item FROM 1 FOR 1) = 'A' THEN 
       UPDATE SET price = price*2 WHERE CURRENT OF defaultvalue;
     ELSE
       DELETE WHERE CURRENT OF defaultvalue;
     END IF; 
  END LOOP LabelLoop; 
  CLOSE defaultvalue; 
END;

See

<­UPDATE CURRENT OF Statement UpCursors in SQL CLOSE Statement­>