Action Queries: Update Query

Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but make 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. Last time, I discussed the Delete Query. This time, I'll look at the Update Query.

Update Queries

An Update Query will make bulk changes to records in the record source. . The record source must be updateable. (See
This Recordset Is Not Updateable. Why? For details about updateability.) At its simplest, an Update Query will make changes to every record. For instance, suppose I want to raise the Price of every book in the Books table by 10%. I could create a SQL statement like this:

UPDATE BOOKS SET Price = Price*1.1;

In the Query Builder, it looks like this:

But it's not very often you want to update every record. Usually, you want to update just certain records. To do that, you add a Where condition just as you would in a Select Query (see: Select Queries Part 2: Restricting Rows - the Where Clause). Suppose I wanted to change every book whose price was $25 to $24.95. My SQL statement would be as follows:

UPDATE Books SET Price = 24.95
WHERE Price=25;

Which looks like this in the Query Builder:

Of course, you could also raise the price by 10% for a particular publisher:

UPDATE BOOKS SET Price = Price*1.1
WHERE PubID=2;

Updating from a List

But what if, as I did with the Delete query, you are given a list of ISBN numbers and associated new prices? How could you update your Books table from this list? You SHOULD be able to use a subquery. Something like this:

UPDATE Books SET Books.Price =
(SELECT price FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN)
WHERE ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = UpdateList.ISBN);

Unfortunately, while you could do this in almost any other SQL implementation (SQL Server or Oracle, for instance) you cannot do this in Access SQL. It will give you the "Operation Must Use an Updateable Query" error.

Fortunately, there are a couple of workarounds. One is to use a Join and the other is to use a Domain Aggregate function.

Workaround 1 - the Join

This work around requires you to Join the Books table with the UpdateList table, like this:

UPDATE Books INNER JOIN UpdateList
ON Books.ISBN = UpdateList.ISBN
SET Books.Price = [Updatelist].[price];

In the QBE:

One caveat here is that the Join MUST be an updateable dataset, so there must be a unique index on the Join field (in this case ISBN).

Workaround 2 - DLookup

This work around uses a subquery in the Where clause, but uses a DLookup function in the Set clause:

UPDATE Books SET Books.Price =
DLookUp("Price","UpdateList","ISBN ='" & [ISBN] & "'")
WHERE Books.ISBN
In (SELECT ISBN FROM UpdateList
WHERE Books.ISBN = updateList.ISBN);

This solution does not require a unique index, but it is much slower than the Join solution.

Running an Update Query

There are two buttons for running Action Queries The View button displays the results of the query, that is, which records will be updated. The Run button actually updates the records.

Next up, the Append Query.