Action Queries: Delete Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. I'll start with Delete Queries and discuss the rest in subsequent posts.

Delete Queries

As the name suggests, Delete Queries delete records from a record source, that is, a table, a join, or another query. The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.)

At its simplest, a delete query will delete all records from the table:

DELETE * FROM Books;

But in most cases, you probably want to delete some subset of all the records. For that, you restrict your records with a Where clause, just as with a Select Query(see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to delete all the records from a particular publisher:

DELETE * FROM Books WHERE PubID=1;

In the Query Builder, I generally suggest creating a Select Query for the records you want to delete:

And then convert it to a Delete Query using the Ribbon (or Toolbar in Access 200X):

Now, suppose you are given a list of ISBN numbers that you need to delete from your table. If that list is in a table (DeleteList), you can delete the records from your Books table with a subquery using the IN operator, like this:

DELETE ISBN FROM Books
WHERE ISBN In (Select ISBN from DeleteList);

In the query builder, it looks like this:

The Select subquery will create a list for the IN operator to supply ISBN numbers to the Delete query.

Running an Action Query

Running an Action Query is a little different than running a Select Query. With a select query, the View and Run buttons do exactly the same thing: display the results of the query. With Action Queries, however, the two buttons do different things. The View button displays the results of the query, that is, which records will be deleted, updated, inserted, or appended. The Run button actually executes the action.

Next time, I'll discuss the Update Query.

.