DELETE

The DELETE data manipulation statement deletes one or more rows from a table.
idmscu19
The DELETE data manipulation statement deletes one or more rows from a table.
This article describes the following information:
2
2
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.
    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.
    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
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.
Using Virtual Foreign Keys in a Simple DELETE Statement
The following DELETE statement deletes the rows from the EMPLOYEE record for employees in the department whose ROWID is x’50000201000000008’. FKEY_DEPT_EMPL is a virtual foreign key referencing the owner DEPARTMENT record's virtual primary key ROWID:
DELETE FROM EMPLOYEE     WHERE FKEY_DEPT_EMPL = X'5000020100000008';
Using Virtual Foreign Keys in a DELETE Statement using a Subquery
The following DELETE statement deletes the rows from the EMPLOYEE record for employees in the Sales department:
DELETE FROM EMPLOYEE WHERE FKEY_DEPT_EMPL =       (SELECT ROWID FROM DEPARTMENT WHERE DEPT_NAME = ‘Sales’);   
More Information