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

Delete method ("DAO Language Reference"), DROP statement, Execute method ("DAO Language Reference"), FROM clause, IN clause, INNER JOIN operation, SELECT statement, UPDATE statement, WHERE clause.

Specifics (Microsoft Access)

In Microsoft Access, no query result set 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

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.

Sub DeleteX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Delete employee records where title is Trainee.    
    dbs.Execute "DELETE * FROM " _
        & "Employees WHERE Title = 'Trainee';"
    
    dbs.Close

End Sub

Example (Microsoft Access)

To try the following example, add a few records to the Employees table with the Title value of "Trainee".

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';