DELETE statement (positioned) [ESQL] [SP]

Deletes the data at the current location of a cursor.

Syntax

DELETE [ [FROM ]table ] WHERE CURRENT OF cursor-name

cursor-name :  identifier | hostvar

table : [ owner.]table-or-view [ [ AS ] correlation-name ]

owner : identifier

table-or-view : identifier

correlation-name : identifier

Remarks

This form of the DELETE statement deletes the current row of the specified cursor. The current row is defined to be the last row fetched from the cursor.

The table from which rows are deleted is determined as follows:

If no FROM clause is included, the cursor must be on a single table only.
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.
If a FROM clause is included, table must unambiguously identify an updatable table in the cursor. If acorrelation-name is specified, the server attempts to match that correlation name with a correlation name specified in the underlying cursor. If a correlation name is not specified in the DELETE statement, and a table owner is not specified, then the server attempts to match table-or-view with an updatable table in the underlying cursor. table-or-view is first matched against any correlation names.
- If a correlation name exists in the underlying cursor, table-or-view may be matched with the corresponding correlation name.
- If a correlation name does not exist, table-or-view must unambiguously match a table name in the cursor.
If a FROM clause is included, and a table owner is specified, table must unambiguously match an updatable table in the underlying cursor.
The positioned DELETE statement can be used on a cursor open on a view as long as the view is updatable.

Privileges

You must be the table owner, or have DELETE privilege on the table.

Side effects

None.

Standards

ANSI/ISO SQL Standard
The DELETE statement (positioned) is a Core Feature. The ability to use a positioned DELETE statement from within an Embedded SQL program is part of optional ANSI/ISO SQL Language Feature B031, "Basic dynamic SQL".

The FROM keyword is mandatory in the ANSI/ISO SQL Standard, but optional in the software. The range of cursors that can be updated may contain extensions that are not in the standard if the ansi_update_constraints option is set to Off.

Example

The following statement removes the current row in the cursor cur_employee from the database.

DELETE
WHERE CURRENT OF cur_employee;