DELETE

This statement deletes specified rows from a database table.

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 
alias-name ::= user-defined-name (Alias-name is not allowed if a 
second FROM clause is used. See FROM Clause .) 
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

INSERT, UPDATE, and DELETE statements behave in an atomic manner. That is, if an insert, update, or delete of more than one row fails, then all insertions, updates, or deletes 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" 
"[Pervasive][ODBC Client Interface][LNA][Pervasive] 
[ODBC Engine Interface]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" 
"[Pervasive][ODBC Client Interface][LNA][Pervasive] 
[ODBC Engine Interface]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 statements 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'