Progress
Embedded SQL-92
Guide and Reference


UPDATE or DELETE the Current Row

ESQL allows UPDATE and DELETE operations on the row that a cursor is pointing to in the active set. You can implement these operations by using the CURRENT OF cursor construct in the WHERE clause of either an UPDATE or a DELETE statement.

This is the format of a WHERE clause with a CURRENT OF construct:

SYNTAX
WHERE CURRENT OF cursor_name ... ; 

EXAMPLE

This code fragment illustrates how to use the CURRENT OF cursor construct in an UPDATE statement. The example updates the qty column of the current row, which is where the cursor is positioned in the active set:

EXEC SQL
     DECLARE ord_cur CURSOR FOR
          SELECT product, qty
          FROM orders
          WHERE order_no = :order_no_v
          FOR UPDATE OF qty ;
EXEC SQL OPEN ord_cur ;
 
EXEC SQL FETCH ord_cur INTO :product_v, :qty_v ;
 
EXEC SQL
     UPDATE orders
          SET qty = :qty_v + 1000
          WHERE CURRENT OF ord_cur ; 

You can execute a positioned update only on an open cursor, and the cursor must be declared for a SELECT statement with a FOR UPDATE clause.

A positioned delete operation deletes the row that the cursor is currently positioned to in the active set. After a positioned delete operation, the cursor is positioned before the row immediately following the deleted row, or after the last row if no following row exists.

EXAMPLE

The following code fragment shows how to use positioned delete to delete the current row from the orders table:

EXEC SQL
     DECLARE ord_cur CURSOR FOR
          SELECT product, qty
          FROM orders
          WHERE order_no = :order_no_v ;
 
EXEC SQL OPEN ord_cur ;
 
EXEC SQL FETCH ord_cur INTO :product_v, :qty_v ;
 
EXEC SQL
     DELETE FROM orders WHERE CURRENT OF ord_cur ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095