DevGuru Jet SQL DELETE Statement

Quick References

Features

Partners


			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.