STATEMENT: DELETE
DELETE [table,*] FROM table WHERE criteria
The DELETE statement creates a query that
removes records from one or more tables. You can totally empty a
table of all records while leaving the table structure and
properties, such as attributes and indexes, intact:
DELETE * FROM Residents;
...or you can be more specific and just
delete those records that meet certain criteria:
DELETE * FROM Residents
WHERE Occupation = 'Teacher';
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.
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|