Deletes a row from a table or view.
1 – Environment
You can use the DELETE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
DELETE FROM --+-> <table-name> ---+-+-----------------------+-+
+-> <view-name> ----+ +-> <correlation-name> -+ |
+-------------------------------------------------------------+
++--------------------------------------------------------+---->
+-> WHERE -+-> predicate --+---------------------------+-+
| +> optimize-clause ---------+ |
+-> CURRENT OF <cursor-name> -----------------+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
3 – Arguments
3.1 – correlation_name
Specifies a name that identifies the table or view in the
predicate of the DELETE statement. See the User_Supplied_Names
HELP topic for more information about correlation names.
3.2 – CURRENT_OF
If the WHERE clause uses CURRENT OF cursor-name, SQL deletes only
the row on which the named cursor is positioned.
The cursor must have been named previously in a DECLARE CURSOR
statement, must be open, and must be positioned on a row. In
addition, the FROM clause of the SELECT statement within the
DECLARE CURSOR statement must refer to the table or view that is
the target of the DELETE statement.
3.3 – OPTIMIZE_AS
Assigns a name to the query.
3.4 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
3.5 – OPTIMIZE_USING
Names the query outline to be used with the DELETE statement
even if the outline ID for the query and for the outline are
different.
A query outline is an overall plan for how a query can be
implemented. See the CREATE OUTLINE statement for additional
information.
3.6 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
3.7 – predicate
If the WHERE clause includes a predicate, all the rows of the
target table for which the predicate is true are deleted. See the
Predicates HELP topic for more information on predicates.
3.8 – table-name
Specifies the name of the target table from which you want to
delete a row.
3.9 – view-name
Specifies the name of the view from which you want to delete a
row.
3.10 – WHERE
Specifies the rows of the target table or view that will be
deleted. If you omit the WHERE clause, SQL deletes all rows of
the target table or view. You can specify either a predicate or a
cursor name in the WHERE clause.
4 – Examples
Example 1: Deleting all information about an employee
To delete all the information about an employee, you need to
delete rows from several tables within a single transaction. This
program fragment deletes the rows from all the result tables that
contain information about an employee. Note that all the DELETE
operations are included in one transaction so that no employee's
records are only partially deleted.
DISPLAY "Enter the ID number of employee".
DISPLAY "whose records you want to delete: "
WITH NO ADVANCING.
ACCEPT EMP-ID.
EXEC SQL
DECLARE TRANSACTION READ WRITE
RESERVING EMPLOYEES FOR PROTECTED WRITE,
JOB_HISTORY FOR PROTECTED WRITE,
SALARY_HISTORY FOR PROTECTED WRITE,
DEGREES FOR PROTECTED WRITE
END-EXEC
EXEC SQL
DELETE FROM EMPLOYEES E
WHERE E.EMPLOYEE_ID = :EMP-ID
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
GO TO ERROR-PAR
END-IF
EXEC SQL
DELETE FROM JOB_HISTORY JH
WHERE JH.EMPLOYEE_ID = :EMP-ID
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
GO TO ERROR-PAR
END-IF
EXEC SQL
DELETE FROM SALARY_HISTORY SH
WHERE SH.EMPLOYEE_ID = :EMP-ID
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
GO TO ERROR-PAR
END-IF
EXEC SQL
DELETE FROM DEGREES D
WHERE D.EMPLOYEE_ID = :EMP-ID
END-EXEC
IF SQLCODE < 0 THEN
EXEC SQL ROLLBACK END-EXEC
GO TO ERROR-PAR
END-IF
Example 2: Deleting selected rows from a table
The following statement deletes all rows from the EMPLOYEES table
where the employee SALARY_AMOUNT is greater than $75,000. The
EMPLOYEES and SALARY_HISTORY tables are both in the database with
the alias PERS.
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> DELETE FROM PERS.EMPLOYEES E
cont> WHERE EXISTS ( SELECT *
cont> FROM PERS.SALARY_HISTORY S
cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND S.SALARY_AMOUNT > 75000
cont> ) ;
7 rows deleted
Example 3: Deleting rows from a table specifying an outline name
The following example shows the syntax used to define the DEL_
EMP_75000 outline:
SQL> CREATE OUTLINE DEL_EMP_75000
cont> FROM
cont> (DELETE FROM EMPLOYEES E
cont> WHERE EXISTS ( SELECT *
cont> FROM SALARY_HISTORY S
cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND S.SALARY_AMOUNT > 75000
cont> );
The following query specifies the DEL_EMP_75000 outline:
SQL> DELETE FROM EMPLOYEES E
cont> WHERE EXISTS ( SELECT *
cont> FROM SALARY_HISTORY S
cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND S.SALARY_AMOUNT > 75000
cont> )
cont> OPTIMIZE USING DEL_EMP_75000;
~S: Outline DEL_EMP_75000 used
.
.
.
7 rows deleted