MS-Access / Getting Started

---

SQL Action Queries

Use SQL action queries to delete, insert, or update data or to create a new table from existing data. Action queries are particularly powerful because they allow you to operate on sets of data, not single rows. For example, an UPDATE statement or a DELETE statement affects all rows in the underlying tables that meet the selection criteria you specify.

DELETE Statement

Deletes one or more rows from a table or a query. The WHERE clause is optional. If you do not specify a WHERE clause, all rows are deleted from the table or the query that you specify in the FROM clause. If you specify a WHERE clause, the database applies the search condition to each row in the table or the query, and only those rows that evaluate to True are deleted.

Syntax

DELETE [<select-list>]
  FROM {table-name [[AS] correlation-name] |
   select-query-name [[AS] correlation-name] |
   <joined table>},...
  [IN <source specification>]
  [WHERE <search-condition>];

where <select-list> is

[* | table-name.*]

and where <joined table> is

({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
ON <join-specification>)

Notes When you specify a query name in a DELETE statement, the query must not be constructed using the UNION query operator. The query also must not contain an SQL aggregate function, the DISTINCT keyword, a GROUP BY or HAVING clause, or a subquery that references the same base table as the DELETE statement.

When you join two or more tables in the FROM clause, you can delete rows only from the many side of the relationship if the tables are related one-to-many; if the tables are related one-to-one, you can delete rows from either side. When you include more than one table in the FROM clause, you must also specify from which table the rows are to be deleted by using table name.* in the <select-list>. When you specify only one table in the FROM clause, you do not need to provide a <select-list>.

You can supply a correlation name for each table or query name. You can use this correlation name as an alias for the full table name when qualifying column names in the WHERE clause and in subclauses. You must use a correlation name when referring to a column name that occurs in more than one table in the FROM clause.

If you use a subquery in the <search-condition>, you must not reference the target table or the query or any underlying table of the query in the subquery.

Examples

To delete all rows in the tblContactProducts table, enter the following:

DELETE FROM tblContactProducts;

To delete all rows in the tblContactEventsHistory table for events that occurred before January 1, 2010, enter the following (qxmplDeleteOldEventHistory):

DELETE tblContactEventsHistory.*
  FROM tblContactEventsHistory
  WHERE tblContactEventsHistory.ContactDateTime < #01/01/2010#;