Statements › DELETE

DELETE

The DELETE data manipulation statement deletes one or more rows from a table.

Authorization

To issue a DELETE statement, you must:

• Hold the DELETE privilege on or own the table, view, or table procedure named in the FROM parameter
• Hold the SELECT privilege on or own each table, view, and table procedure explicitly named in a subquery in the search condition in the WHERE parameter

Additional authorization requirements apply to:

• A view named in the FROM parameter, each view named in the FROM parameter of such a view, each view named in the FROM parameters of those views, and so forth.

  For any such view, the owner of the view must hold the grantable DELETE privilege on or own the table, view, or table procedure named in the FROM parameter of the view definition.

• Each view named in the FROM parameter of a subquery in the search condition, each view named in the FROM parameter of such a view, each view named in the FROM parameters of those views, and so forth.

  For any such view, the owner of the view must hold the grantable SELECT privilege on or own each table, view, and table procedure named in the FROM parameter of the view definition.

Syntax

►►─── DELETE FROM table-reference ───┬─────────┬──────────────────────────────►
                                     └─ alias──┘

 ►─┬─────────────────────────────────────────────────────┬────────────────────►◄
   └─ WHERE ─┬─ search-condition ──────────────────────┬─┘
             └─ CURRENT OF ─┬─ cursor-name ─────────┬──┘
                            └─ dynamic-name-clause ─┘

Expansion of dynamic-name-clause

►►─┬─────────────┬─ cursor-name ──────────────────────────────────────────────►◄
   ├─ LOCAL ◄ ─┬─┘
   └─ GLOBAL ──┘

Parameters

FROM table-reference

Specifies the table, view, or table procedure from which rows are to be deleted. Table-reference must not specify a procedure or a joined table. If table-reference identifies a view:

• The view must be updateable
• The applicable rows are deleted from the table from which the view is derived

For expanded table-reference syntax, see Expansion of Table-reference.

alias

Defines a new name to be used to identify the table, view, or table procedure within the DELETE statement. Alias must be a 1- through 18-character name that follows the conventions for SQL identifiers.

WHERE

Restricts the rows to be deleted. If the DELETE statement does not include the WHERE parameter, CA IDMS deletes all rows from the specified table, view, or table procedure.

search-condition

Specifies criteria a row must meet to be deleted:

• When the value of search-condition is true, the row is deleted
• When the value of search-condition is false or unknown, the row is not deleted

For expanded search-condition syntax, see Expansion of Search-condition.

CURRENT OF

Specifies only the row that corresponds to the current row of the named cursor is to be deleted.

cursor-name

Identifies the cursor whose current row will be deleted. Cursor-name must identify an open cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.

Note: This option may only be used in a DELETE statement embedded in an application program.

dynamic-name-clause

Identifies the cursor whose current row will be deleted.

Note: This option may only be used in a DELETE statement dynamically compiled using a PREPARE or EXECUTE IMMEDIATE statement.

Parameters for Expansion of dynamic-name-clause

LOCAL

Indicates the named cursor has a local scope and was defined using a DECLARE CURSOR statement or an ALLOCATE CURSOR statement. The default is LOCAL.

GLOBAL

Indicates the named cursor was created by an ALLOCATE CURSOR statement and is global in scope.

cursor-name

Specifies the name of the cursor as an identifier. Cursor-name must identify an open cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.

Usage

Searched Deletes

A DELETE statement that include the WHERE search-condition parameter or does not include the WHERE parameter at all is called a searched delete. Searched deletes may be entered through the Command Facility, executed dynamically, or embedded within application programs.

Positioned Deletes

A DELETE statement that includes the WHERE CURRENT OF CURSOR parameter is called a positioned delete. The cursor identified in the positioned delete statement must be updateable. Positioned deletes are valid only from within an application program.

Dynamic Positioned Deletes

A dynamic positioned DELETE statement is one that references a dynamic cursor. Such a DELETE statement may be embedded within an application program or created dynamically using a PREPARE or EXECUTE IMMEDIATE statement.

A positioned DELETE statement embedded in an application program may reference a static cursor or a dynamic cursor. A positioned DELETE statement created dynamically using a PREPARE or EXECUTE IMMEDIATE statement can only reference a dynamic cursor.

Ambiguous Cursor References

When a dynamic positioned DELETE statement is being created by a PREPARE or EXECUTE IMMEDIATE statement, it is possible that CA IDMS may not be able to determine which cursor is being referenced. This occurs if the application program contains a DECLARE CURSOR statement that defines a cursor having the referenced name and the program has also executed an ALLOCATE cursor statement that creates a cursor with the same name and a local scope. Under these conditions, CA IDMS cannot determine which of the two cursors is being referenced. To avoid such problems, it is advisable to use different names for cursors that are declared from those that are allocated with a local scope.

Restrictions on table-reference

In a searched delete, the table, view, or table procedure named in the FROM parameter of the DELETE statement cannot also be named in the FROM parameter of any subquery included in the specified search condition or, in the case of a view, in any search condition used in the view definition. This means that you cannot delete data from a table from which you select in a subquery.

In a positioned delete, the table, view, or table procedure named in the FROM parameter of the DELETE statement must also be named in the FROM parameter of the query specification used in the definition of the named cursor.

Restriction for Tables in Referential Constraints

If the table referenced in a DELETE statement is the referenced table in a referential constraint, and the referencing table in the referential constraint includes one or more rows whose key-column values match those of a row to be deleted, CA IDMS returns an error and does not delete the row.

Cursor Position After a Positioned Delete

After a positioned delete, the position of the cursor named in the DELETE statement is before the row that immediately followed the deleted row. If the deleted row was the last row in the result table associated with the cursor, the position of the cursor is after the last row.

Transaction State for the DELETE Statement

CA IDMS processes a DELETE statement only when the transaction state is read write.

Deleting Through a View

If you specify a view in the FROM clause of a DELETE statement, the view must be updateable, and only rows that can be retrieved through the view can be deleted through the view.

Examples

Requesting a Searched Delete

The following DELETE statement deletes rows from the BENEFITS table for employees that have been terminated (status T):

delete from benefits
   where emp_id in
      (select emp_id
         from employee
         where status = 'T');

Requesting a Positioned Delete

The following DELETE statement deletes the row of the EST_COST table that corresponds to the current row of the EST_COST_CURSOR cursor:

EXEC SQL
   DELETE FROM EST_COST
      WHERE CURRENT OF EST_COST_CURSOR
END-EXEC

Deleting All Rows

The following DELETE statement deletes all rows from the PROPOSED_BUDGET table:

delete from proposed_budget;

A Positioned DELETE Referencing a DECLAREd Cursor

The following statement deletes the current row of the cursor C1. C1 may be a dynamic or static cursor, and it must have been defined using a DECLARE CURSOR statement:

EXEC SQL
  DELETE FROM EMPLOYEE WHERE CURRENT OF C1
END-EXEC

A Positioned DELETE Referencing an ALLOCATEd Cursor

The following statement deletes the current row of a cursor whose name is specified in the variable CNAME. The referenced cursor must have been defined using an ALLOCATE CURSOR statement:

EXEC SQL
  DELETE FROM EMPLOYEE WHERE CURRENT OF :CNAME
END-EXEC

A Dynamically-compiled Positioned DELETE Statement

The following statement deletes the current row of local cursor C1. C1 may have been defined using a DECLARE CURSOR statement or an ALLOCATE CURSOR statement. In either case, the cursor name in the DELETE statement is specified as an identifier rather than as a literal or host variable:

EXEC SQL
  EXECUTE IMMEDIATE
  'DELETE FROM EMPLOYEE WHERE CURRENT OF LOCAL C1'
END-EXEC

Note: The keyword LOCAL is unnecessary since it is the default. Regardless of whether it is specified, if two local cursors named C1 have been defined, one using a DECLARE CURSOR statement and one using an ALLOCATE CURSOR statement, the EXECUTE IMMEDIATE statement fails on an ambiguous cursor error.

More Information

• For more information about updateable views, see CREATE VIEW.
• For more information about defining and manipulating cursors, see CLOSE, DECLARE CURSOR, FETCH, and OPEN.
• For more information about updateable result tables, see DECLARE CURSOR