The DELETE statement is used to delete records from a database table. Its general format for deleting a single record is shown below:
|
DELETE FROM TableName WHERE criteria |
The keywords DELETE FROM are followed by the name of the table from which a record is to be deleted. The WHERE clause supplies the identification of the record. No set of records is returned by a DELETE statement.
The WHERE Clause
The keyword WHERE is followed by one or more selection criteria. A common way of specifying a record to delete is by a match to its unique "key" field.
DELETE FROM Products WHERE ItemNo = '99999'You can, though, use any of the common conditional operators,
| = | (equal to) |
| <> | (not equal to) |
| < | (less than) |
| > | (greater than) |
| <= | (less than or equal to) |
| => | (equal to or greater than) |
to identify the record. Plus, you can combine tests using the logical operators AND, OR, and NOT to expand or contract your selection:
DELETE FROM Products WHERE ItemQuantity = 0 AND NOT ItemType = 'Software'In this case, more than one record may be deleted. If no WHERE clause is provided, all records in the table are deleted.
When matching against a text field, the criterion value must be enclosed in single quotes; in testing against a numeric field, the data value is not enclosed in quotes; in testing against a date/time field, the criterion value is surrounded by # symbols.
If a comparison text field contains apostrophes, they must be replaced with double apostrophes. See the discussion about apostrophes in data values on the SELECT page.
As in the case of SELECT and INSERT statements, the DELETE statement normally is composed in a script using combinations of literal strings and variables.
SQLString = "DELETE FROM Products WHERE PurchaseDate < #$TheDate# OR (ItemQty < $TheQuantity AND ItemType = '$TheType')"
This code resolves into a DELETE statement resembling the following:
SQLString = "DELETE FROM Products WHERE PurchaseDate < #07/15/02# OR (ItemQty < 10 AND ItemType = 'Software')"