DELETE
This statement deletes specified rows from a database table or view.
Syntax
DELETE [ FROM ] < table-name | view-name > [ alias-name ]
[ FROM table-reference [, table-reference ] ...
[ WHERE search-condition ]
table-name ::= user-defined-name
view-name ::= user-defined-name
table-reference ::= { OJ outer-join-definition }
| [
db-name.]
table-name [ [
AS ]
alias-name ]
| [
db-name.]
view-name [ [
AS ]
alias-name ]
| join-definition
| ( join-definition )
| (
table-subquery )[
AS ]
alias-name [ (
column-name [ ,
column-name ]... ) ]
outer-join-definition ::= table-reference outer-join-type JOIN table-reference ON search-condition
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]
search-condition ::= search-condition AND search-condition
| search-condition OR search-condition
| NOT search-condition
| ( search-condition )
| predicate
db-name ::= user-defined-name
view-name ::= user-defined-name
join-definition ::= table-reference [ join-type ] JOIN table-reference ON search-condition
| table-reference CROSS JOIN table-reference
| outer-join-definition
join-type ::= INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ]
table-subquery ::=
query-specification [ [
UNION [
ALL ]
query-specification ]... ]
Remarks
DELETE statements, as with INSERT and UPDATE, behave in an atomic manner. That is, if a deletion of more than one row fails, then all deletions of previous rows by the same statement are rolled back.
FROM Clause
Some confusion may arise pertaining to the second optional FROM clause and references to the table whose rows are being deleted (referred to as the “delete table”). If the delete table occurs in the second FROM clause, then one of the occurrences is the same instance of the table whose rows are being deleted.
For example, in the statement DELETE t1 FROM t1, t2 WHERE t1.c1 = t2.c1, the t1 immediately after DELETE is the same instance of table t1 as the t1 after FROM. Therefore, the statement is identical to DELETE t1 FROM t2 WHERE t1.c1 = t2.c1.
If the delete table occurs in the second FROM clause multiple times, one occurrence must be identified as the same instance as the delete table. The second FROM clause reference that is identified as the same instance as the delete table is the one that does not have a specified alias.
Therefore, the statement DELETE t1 FROM t1 a, t1 b WHERE a.c1 = b.c1 is invalid because both instances of t1 in the second FROM clause contain an alias. The following version is valid: DELETE t1 FROM t1, t1 b WHERE t1.c1 = b.c1.
The following conditions apply to the second FROM clause:
•If the DELETE statement contains an optional second FROM clause, the table reference prior to the FROM clause cannot have an alias specified. For example, DELETE t1 a FROM t2 WHERE a.c1 = t2.c1 returns the following error:
SQL_ERROR (-1)
SQLSTATE of "37000"
"Table alias not allowed in UPDATE/DELETE statement with optional FROM."
A valid version of the statement is DELETE t1 FROM t2 WHERE t1.c1 = t2.c1 or DELETE t1 FROM t1 a, t2 WHERE a.c1 = t2.c1.
•If more than one reference to the delete table appears in the second FROM clause, then only one of the references can have a specified alias. For example, DELETE t1 FROM t1 a, t1 b WHERE a.c1 = b.c1 returns the following error:
SQL_ERROR (-1)
SQLSTATE of "37000"
"The table t1 is ambiguous."
In the erroneous statement, assume that you want table t1 with alias “a” to be the same instance of the delete table. A valid version of the statement is DELETE t1 FROM t1, t1 b WHERE t1.c1 = b.c1.
•The second FROM clause is supported in a DELETE statement only at the session level. The FROM clause is not supported if the DELETE statement occurs within a stored procedure.
Examples
The following statement deletes the row for first name Ellen from the person table in the sample database.
DELETE FROM person where First_Name = 'Ellen'
The following statement deletes the row for Modern European History (HIS 305) from the course table in the sample database:
DELETE FROM Course WHERE Name = 'HIS 305'