Queries - Update queries

An update query can make global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

To create an update query

In the following example a hardware wholesale supplier has decided to give a discount of 10% on all his products.  The current prices are shown below.

To calculate the discount on all the products in the table, the update query will be used.  Note the names of the tables in the database.

In table design view add a new field for the Wholeprice with discount (Try to keep the names short.), data type Currency.  At the moment there are no prices in that field, they will be added after the update query.

To create an update query, click on Queries.  Click on Create query in Design View.

Add the required table, then close the Show Table dialog box.

From the field list drag the field Wholeprice with discount to the query design grid. 

On the toolbar, click the arrow next to Query type and select Update Query.

Note the new cells in the design grid.

In the Update To cell for the field you want to update, type the expression or value you want to use to change the fields, as shown in the following illustration. The field name must be enclosed in square brackets [] and must be copied exactly.   (Multiplying by 0.9 means to give a 10% discount.)  Click Run on the toolbar to update all the records.

If you are sure it is correct, click Yes.

Save the query with a clear name.

Note the image to indicate that an update query has been done.

Open the table and note the difference.  The wholesale prices have been discounted by 10%.

You can use the following Update expressions

This work may be freely copied for non-commercial use only. Copyright © Pam Miller