1  DELETE
   Deletes a row from a table or view.
 

2  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> -----------------------+ |
                  +---------------- <----------------------------+
 

 

2  Arguments
 

3  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  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  OPTIMIZE_AS

   Assigns a name to the query.
 

3  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  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  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  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  table-name

   Specifies the name of the target table from which you want to
   delete a row.
 

3  view-name

   Specifies the name of the view from which you want to delete a
   row.
 

3  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.
 

2  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