DELETE statement (positioned) [ESQL] [SP]

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

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.

Must have DELETE permission on tables used in the cursor.

None.

SQL/2008 The DELETE statement (positioned) is a core feature of the SQL/2008 standard. The ability to use a positioned DELETE statement from within an embedded SQL program is part of optional SQL language feature B031, "Basic dynamic SQL".
The FROM keyword is mandatory in SQL/2008, but optional in SQL Anywhere. The range of cursors that can be updated may contain vendor extensions if the ansi_update_constraints option is set to Off.

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

DELETE
WHERE CURRENT OF cur_employee;

DELETE statement (positioned) [ESQL] [SP]

Syntax

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example