ASA Programming Guide
  Using SQL in Applications
    Working with cursors

Modifying rows through a cursor

Cursors can do more than just read result sets from a query. You can also modify data in the database while processing a cursor. These operations are commonly called positioned insert, update, and delete operations, or PUT operations if the action is an insert.

Not all query result sets allow positioned updates and deletes. If you carry out a query on a non-updatable view, then no changes occur to the underlying tables. Also, if the query involves a join, then you must specify which table you wish to delete from, or which columns you wish to update, when you carry out the operations.

Inserts through a cursor can only be executed if any non-inserted columns in the table allow NULL or have defaults.

If multiple rows are inserted into a value-sensitive (keyset driven) cursor, they appear at the end of the cursor result set. The rows appear at the end even if they do not match the WHERE clause of the query or if an ORDER BY clause would normally have placed them at another location in the result set. This behavior is independent of programming interface. For example, it applies when using the embedded SQL PUT statement or the ODBC SQLBulkOperations function. The value of an autoincrement column for the most recent row inserted can be found by selecting the last row in the cursor. For example, in embedded SQL the value could be obtained using FETCH ABSOLUTE -1 cursor-name. As a result of this behavior, the first multiple-row insert for a value-sensitive cursor may be expensive.

ODBC, embedded SQL, and Open Client permit data modification using cursors, but JDBC 1.1 does not. With Open Client, you can delete and update rows, but you can only insert rows on a single-table query.

Which table are rows deleted from? 

If you attempt a positioned delete through a cursor, the table from which rows are deleted is determined as follows:

1. If no FROM clause is included in the DELETE statement, the cursor must be on a single table only.

2. If the cursor is for a joined query (including using a view containing a join), then the FROM clause must be used. Only the current row of the specified table is deleted. The other tables involved in the join are not affected.

3. If a FROM clause is included, and no table owner is specified, the table-spec value is first matched against any correlation names.

   For more information, see the FROM clause.

4. If a correlation name exists, the table-spec value is identified with the correlation name.

5. If a correlation name does not exist, the table-spec value must be unambiguously identifiable as a table name in the cursor.

6. If a FROM clause is included, and a table owner is specified, the table-spec value must be unambiguously identifiable as a table name in the cursor.

7. The positioned DELETE statement can be used on a cursor open on a view as long as the view is updatable.