DELETE

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

Invocation

This statement can be used in an application program using the CLI functions or issued through the CLP.

Syntax 

>>-DELETE FROM--table-name--+-----------------------------+----><
                            '-WHERE--| search_condition |-'
 
search_condition:
 
   .----------------------------------------------.
   V                                              |
|----+------------------------------------------+-+-------------|
     '-+-AND-+--+-----+--+-| predicate |------+-'
       '-OR--'  '-NOT-'  '-(search_condition)-'
 
predicate:
 
|--+-| basic predicate |-+--------------------------------------|
   +-| IN predicate |----+
   +-| LIKE predicate |--+
   '-| NULL predicate |--'
 
basic predicate:
 
|--| expression |--+-=--+--| expression |-----------------------|
                   +-<>-+
                   +-<--+
                   +->--+
                   +-<=-+
                   +->=-+
                   '-||-'
 
IN predicate:
 
                                .-,--------------------.
                                V                      |
|--| expression |--+-----+--IN----(--| expression |--)-+--------|
                   '-NOT-'
 
LIKE predicate:
 
|--| expression |--+-----+--LIKE--| expression |----------------|
                   '-NOT-'
 
NULL predicate:
 
                  (1)
|--| expression |-------IS--+-----+--NULL-----------------------|
                            '-NOT-'
 
expression:
 
   .-operator--------------------.
   V                             |
|----+---+--+-literal----------+-+------------------------------|
     +-+-+  +-column-name------+
     '---'  +-(expression)-----+
            +-special register-+
            '-function---------'
 
operator:
 
|--+-/-+--------------------------------------------------------|
   +-*-+
   +-+-+
   '---'

Anmerkungen:

  1. BLOB expressions are only allowed in NULL predicates.

Description

FROM table-name
Identifies the table from which rows are to be deleted. The name must identify a table that exists in the catalog, but it must not identify a catalog table.
WHERE
Specifies a condition that selects the rows to be deleted. The clause can be omitted or a search condition specified. If the clause is omitted, all rows of the table are deleted.
search_condition
A search_condition specifies a condition that is true, false, or unknown about a given row.

The result of a search_condition is derived by applying the specified logical operators (AND, OR, NOT) to the result of each specified predicate. A predicate compares two values. If logical operators are not specified, the result of the search condition is the result of the specified predicate.

Search conditions within parentheses are evaluated first. If the order of evaluation is not specified by parentheses, NOT is applied before AND, and AND is applied before OR. The order in which operators at the same precedence level are evaluated is undefined to allow for optimization of search conditions.

The search_condition is applied to each row of the table and the deleted rows are those for which the result of the search_condition is true.

Each column-name in the search condition must identify a column of the table.

NOT
If NOT is specified, the result of the predicate is reversed.
expression
Identifies an operand of the predicate. The expression can be a literal, column name, special register, or function.

Arithmetic operations on BLOB(n), DATE, TIME, and TIMESTAMP data types are not supported.

literal
A literal can be a value of data type INTEGER, SMALLINT, DECIMAL, CHAR(n), VARCHAR(n), BLOB(n), DATE, TIME, or TIMESTAMP.
column-name
Identifies the column that is an operand of the predicate.
special register
Identifies the special register that is an operand of the predicate. The special registers CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP can be used to produce the current date, time, or timestamp.
function
Can include only the MOD, LENGTH, and RTRIM functions.
relational operator
Can be any of the following operators:

=
Equal to.

<>
Not equal to.

<
Less than.

>
Greater than.

<=
Less than or equal to.

>=
Greater than or equal to.

LIKE
Matches one character string. Use a single-byte character-set (SBCS) underscore to refer to one SBCS character. Use a double-byte character-set (DBCS) underscore to refer to one DBCS character. For example, the condition WHERE PART_NUMBER LIKE '_0' returns all 2-digit part numbers ending in 0 (20, 30, and 40, for example). Use a percent (either SBCS or DBCS) to refer to a string of zero or more SBCS or DBCS characters. For example, the condition WHERE DEPT_NUMBER LIKE '2%' returns all department numbers beginning with the number 2 (20, 27, or 234, for example).

NOT LIKE
Does not have at least one of the same characters.

IS NULL
Contains the null value.

IS NOT NULL
Does not contain the null value.
AND
If specified, the logical operator AND is applied to the result of each specified predicate.
OR
If specified, the logical operator OR is applied to the result of each specified predicate.

Rules

None.

Notes

Example

Delete employee number (EMPNO) 003002 from the EMPLOYEE table. 

     DELETE FROM EMPLOYEE
       WHERE EMPNO = '003002'

Zugehörige Referenzen