Introduction

---

The SQL DELETE statement is used to delete one or more rows from a table.

Syntax

---

DELETE FROM table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]

[[INNER | [LEFT | RIGHT] OUTER JOIN] table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE] ON join_condition]

[WHERE predicates]

[COMMIT [INTERVAL commit_interval] FLUSH]

[NOJOINOPTIMIZE]
[JOINOPTIMIZECOSTS]
[NOWHEREJOINS]

Use DELETE to delete one or more rows from one existing table per statement.

FROM Clause

---

The FROM clause specifies the table to use for the DELETE statement. The syntax is as follows:

FROM table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]

Specified table names must follow DBISAM's SQL naming conventions for tables. Please see the Naming Conventions topic for more information.

Use the EXCLUSIVE keyword to specify that the table should be opened exclusively.


JOIN Clauses

---

You may use optional JOIN clauses to specify multiple tables from which a DELETE statement retrieves data for the purpose of deleting records in the target table. The following DELETE statement below deletes data in one table based upon an INNER JOIN condition to another table:

DELETE FROM orders
INNER JOIN customer ON customer.custno=orders.custno
WHERE customer.country='Bermuda'

You can use the AS keyword to specify a table correlation name, or alternately you can simply just specify the table correlation name after the source table name. The following example uses the second method to give each source table a shorter name to be used in qualifying source columns in the query:

DELETE FROM orders o
INNER JOIN customer c ON c.custno=o.custno
WHERE c.country='Bermuda'

Please see the SELECT Statement topic for more information.

WHERE Clause

---

The WHERE clause specifies filtering conditions for the DELETE statement. The syntax is as follows:

WHERE predicates

Use a WHERE clause to limit the effect of a DELETE statement to a subset of rows in the table, and the clause is optional.

The value for a WHERE clause is one or more logical expressions, or predicates, that evaluate to TRUE or FALSE for each row in the table. Only those rows where the predicates evaluate to TRUE are deleted by a DELETE statement. For example, the DELETE statement below deletes all rows where the State column contains a value of 'CA':

DELETE FROM SalesInfo
WHERE (State='CA')

Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria.

Subqueries are supported in the WHERE clause. A subquery works like a search condition to restrict the number of rows deleted by the outer, or "parent" query. Such subqueries must be valid SELECT statements. SELECT subqueries cannot be correlated in DBISAM's SQL, i.e. they cannot refer to columns in the outer (or "parent") statement.

Column correlation names cannot be used in filter comparisons in the WHERE clause. Use the actual column name.

Columns devoid of data contain NULL values. To filter using such column values, use the IS NULL predicate.

The DELETE statement may reference any table that is specified in the FROM, or JOIN clauses in the WHERE clause.

COMMIT Clause

---

The COMMIT clause is used to control how often DBISAM will commit a transaction while the DELETE statement is executing and/or whether the commit operation performs an operating system flush to disk. The DELETE statement implicitly uses a transaction if one is not already active. The default interval at which the implicit transaction is committed is based upon the record size of the table being updated in the query and the amount of buffer space available in DBISAM. The COMMIT INTERVAL clause is used to manually control the interval at which the transaction is committed based upon the number of rows deleted, and applies in both situations where a transaction was explicitly started by the application and where the transaction was implicitly started by DBISAM. In the case where a transaction was explicitly started by the application, the absence of a COMMIT INTERVAL clause in the SQL statement being executed will force DBISAM to never commit any of the effects of the SQL statement and leaves this up to the application to handle after the SQL statement completes. The syntax is as follows:

COMMIT [INTERVAL nnnn] [FLUSH]

The INTERVAL keyword is optional, allowing the application to use the default commit interval but still specify the FLUSH keyword to indicate that it wishes to have the transaction commits flushed to disk at the operating system level. Please see the Transactions and Buffering and Caching topics for more information.

NOJOINOPTIMIZE Clause

---

The NOJOINOPTIMIZE clause causes all join re-ordering to be turned off for a SELECT statement. The syntax is as follows:

NOJOINOPTIMIZE

Use a NOJOINOPTIMIZE clause to force the query optimizer to stop re-ordering joins for a SELECT statement. In certain rare cases the query optimizer might not have enough information to know that re-ordering the joins will result in worse performance than if the joins were left in their original order, so in such cases you can include this clause to force the query optimizer to not perform the join re-ordering.

JOINOPTIMIZECOSTS Clause

---

The JOINOPTIMIZECOSTS clause causes the optimizer to take into account I/O costs when optimizing join expressions. The syntax is as follows:

JOINOPTIMIZECOSTS

Use a JOINOPTIMIZECOSTS clause to force the query optimizer to use I/O cost projections to determine the most efficient way to process the conditions in a join expression. If you have a join expression with multiple conditions in it, then using this clause may help improve the performance of the join expression, especially if it is already executing very slowly.

Please see the Updating Tables and Query Result Sets topic for more information on deleting records in a table.

NOWHEREJOINS Clause

---

The NOWHEREJOINS clause causes the optimizer to treat any join expressions in the WHERE clause (SQL-89-style joins) as normal, un-optimized expressions instead of inner joins. The syntax is as follows:

NOWHEREJOINS

Use a NOWHEREJOINS clause to force the query optimizer to treat any joins in the WHERE clause as normal, un-optimized expressions instead of inner joins. This is very useful when you need the conditions for filtering the results, but do not want to treat them as inner joins because they exhibit a low cardinality (there are lot of matching values). Join conditions with a low cardinality can be slow because they cause a lot of overhead in processing the sets of rows in the DBISAM engine.