JetSQL » Statements » DELETE
- Syntax:
- DELETE [table,*] FROM table WHERE criteria
- table_name
- Specifies the name of the table that the delete operation is to be performed on.
- view_name
- Specifies the name of the view that the delete operation is to be performed on.
- table_source
- A parameter that allows the specification of data from table_source and deletes corresponding rows from the table in the first FROM clause.
- criteria
- Specifies the restricting condition(s) for the row(s) to be deleted.
The DELETE statement creates a query that removes records from one or more tables.
You can use a "cascade delete" operation to remove records from tables that are in a one-to-many relationship with other tables. A cascade delete causes 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.
The Jet database engine will cascade delete if the relationships between tables are configured to enable this option. While Jet SQL gives us a mechanism to establish a relationship (using the CONSTRAINT clause) it does not give us any method to configure relationships to enable cascading operations. However, if a relationship has been configured to enable cascading operations, for example by using Microsoft Access or DAO (Data Access Objects), the Jet SQL DELETE (and UPDATE) statements will cascade.
A DELETE is permanent!
After you remove records using a DELETE statement, you cannot undo the operation. To check which records will be deleted, examine the results of a SELECT query that uses the same criteria.
It is also important to understand, that a DELETE statement deletes entire records, not just data in specified fields. If you just want to delete certain fields, use an UPDATE query that changes the value to NULL.
Examples
Code:
DELETE * FROM Residents;
Output:
(10 row(s) affected)
Explanation:
This example demonstrates how to totally empty a table of all records while leaving the table structure and properties, such as attributes and indexes, intact.
Language(s): MS SQL Server
Code:
DELETE * FROM Residents WHERE Occupation = 'Teacher';
Output:
(3 row(s) affected)
Explanation:
This example is more specific and only deletes those records that meet certain criteria.
Language(s): MS SQL Server
See Also:
