>
DELETE Statement
Description
Creates a delete query that removes records from one
or more of the tables listed in the FROM clause that satisfy the
WHERE clause.
Syntax
DELETE [table.*]
FROM table
WHERE criteria
The DELETE statement has these parts.
Part |
Description |
|
table |
The optional name of the table from
which records are deleted. |
table |
The name of the table from which records
are deleted. |
criteria |
An expression that determines which
records to delete. |
Remarks
DELETE is especially useful when you want to delete
many records.
To drop an entire table from the database, you can
use the Execute method with a DROP statement. If you
delete the table, however, the structure is lost. In contrast,
when you use DELETE, only the data is deleted; the table
structure and all of the table properties, such as field
attributes and indexes, remain intact.
You can use DELETE to remove records from tables
that are in a one-to-many relationship with other tables. Cascade
delete operations cause the records in tables that are on the
many side of the relationship to be deleted when the
corresponding record in the one side of the relationship is
deleted in the query. For example, in the relationship between
the Customers and Orders tables, the Customers table is on the
one side and the Orders table is on the many side of the
relationship. Deleting a record from Customers results in the
corresponding Orders records being deleted if the cascade delete
option is specified.
A delete query deletes entire records, not just data
in specific fields. If you want to delete values in a specific
field, create an update query that changes the values to Null.
Important
- After you remove records using a delete query, you can't
undo the operation. If you want to know which records
were deleted, first examine the results of a select query
that uses the same criteria, and then run the delete
query.
- Maintain backup copies of your data at all times. If you
delete the wrong records, you can retrieve them from your
backup copies.
See Also
DROP Statement, FROM Clause, IN Clause, INNER JOIN
Operation, SELECT Statement, UPDATE Statement, WHERE Clause.
Specifics (Microsoft Access)
In Microsoft Access, no query output or datasheet is
produced when you use the DELETE statement. If you want to know
which records will be deleted, first view the datasheet of a
select query that uses the same criteria, and then run a delete
query.
Example
Some of the following examples assume the existence
of a hypothetical Payroll table.
This example deletes all records for employees whose
title is Trainee. When the FROM clause includes only one table,
you don't have to list the table name in the DELETE statement.
DELETE * FROM Employees WHERE Title = 'Trainee';
This example deletes all records for employees whose
title is Trainee and who also have a record in the Payroll table.
The Employees and Payroll tables have a one-to-one relationship.
DELETE Employees.* FROM Employees INNER JOIN Payroll
ON Employees.EmployeeID = Payroll. EmployeeID
WHERE Employees.Title = 'Trainee';