DELETE Statement (SQL)
See Also Example
You can use DELETE in an action query to create a delete query that removes records listed in the DELETE statement and from the tables listed in the FROM clause.
DELETE is especially useful when you want to delete many records or when the records are in multiple tables.
If you want to delete all of the records in a table, deleting the table itself may be more efficient than executing a delete query. If you delete the table, however, the structure is lost. In contrast, when you use DELETE, only the data is removed; the table structure and all of the table properties, such as field attributes and indexes, remain intact.
If you want to delete only some of the records in a table, include a WHERE clause in your SQL statement. Only records that meet the conditions in the WHERE clause will be deleted.
No records are returned when you use the DELETE statement.
You can use DELETE to remove records from a single table or from multiple tables in a one-to-one relationship. To remove records from tables in a one-to-many relationship (for example, records for all customers in the United Kingdom and all their orders), you must run two queries.
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 empty values.
Important Once you remove records using a delete query, you can't undo the operation. If you want to know which records will be deleted, first examine the results of a select query that uses the same criteria, and then execute 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.
Warning If an SQL statement changes more rows than the internal transaction log can hold, Visual Basic will force the transation to commit the transaction in stages. If your program is interrupted before the operation is completed, only the last, uncompleted stage(s) will be rolled back. In this case only some of the records to be changed will have been affected. If you use a BeginTrans statement before the SQL statement, a trappable error will occur if the transaction log fills prior to the completion of your statement, and all of the changes made by your statement will be automatically rolled back.