Access 101: UPDATE Query

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

In the world of Access queries, the UPDATE query holds a special place.

It's the tool you need when you want to modify existing data in your tables. It's a powerful tool, but with great power comes great responsibility. Used carelessly, it can cause significant data loss. But used wisely, it can save you a lot of time and effort.

What is an UPDATE Query?

In my earlier article, Access 101: What is a Query in Access?, I introduced the CRUD acronym to describe the four basic operations you can perform on data:

• Create
• Read
• Update
• Delete

Not surprisingly, the UPDATE query is the tool for the third operation: Update. It allows you to modify existing data in your tables.

The Basic Structure of an UPDATE Query

The basic structure of an UPDATE query is as follows:

UPDATE tableName
SET field1 = value1, 
    field2 = value2, 
    ..., 
    fieldN = valueN
WHERE condition;

• UPDATE is the command that initiates the query.
• tableName is the name of the table where you want to update data.
• SET is the keyword that precedes the list of fields you want to update and the new values you want to set.
• field1 = value1, field2 = value2, ..., fieldN = valueN is the list of fields you want to update and the new values you want to set. You can update as many fields as you want in a single UPDATE query.
• WHERE condition is an optional clause that you can use to specify which records you want to update. The condition is a logical statement that can be used to filter records. BE WARNED: If you omit the WHERE clause, the UPDATE query will update all records in the table.

An Example UPDATE Query

Let's say we have a table called Employee with fields EmployeeID, FirstName, LastName, Position, and Salary. If we want to give all managers a 10% raise, we'd use the following query:

UPDATE Employee
SET Salary = Salary * 1.1
WHERE Position = 'Manager';

Running an UPDATE Query in Access

To run an UPDATE query in Access, follow these steps:

1. Open your Access database and go to the "Create" tab on the Ribbon.
2. Click on "Query Design" to open a new query.
3. In the "Query Type" group on the Design tab, click "Update".
4. Using the "Add Tables" pane, double-click the table you want to update or click and drag it to the query window.
5. Double-click on the field names in the table in the top half of the query window to add them to the query.
6. In the "Update To" row in the bottom half of the query window, enter the new values you want to set.
7. In the "Criteria" row, enter the conditions that the records must meet to be updated.
8. In the "Query Type" group on the Design tab, click "Select".
9. Click "Run" (!) on the Design tab to preview which records will be updated.
10. In the "Views" group on the Home tab, click "Design View" to switch back to the query designer.
11. If necessary, adjust the conditions in the "Criteria" row and re-run the Select query to confirm everything is good.
12. Once satisfied, click "Update" in the "Query Type" group of the Design tab.
13. Click "Run" (!) on the Design tab to execute the query.
14. Press [Ctrl] + [S] if you want to save the query for later.

Err on the Side of Too Many WHERE Clauses

It's all too easy to run an UPDATE query and accidentally change more records than you intended.

One technique I use–especially when running ad hoc UPDATE queries against live data–is to add redundant WHERE clauses.  Here's an example from an earlier article I wrote:

Use Excessive WHERE Clauses

If you only intend to change a single record, find that record's primary key and include it in the WHERE clause.  Don't rely on a date-vendor combination to update a single invoice record only to find out too late that that vendor had several invoices on that particular date.  Instead of changing one record, you accidentally changed a dozen!

For example, instead of this...

UPDATE Invoice
SET PmtAmt = 42
WHERE VendorID = 10
  AND PurchaseDate = '2022-1-12'

...use this...

UPDATE Invoice
SET PmtAmt = 42
WHERE InvoiceID = 1783
  AND VendorID = 10
  AND PurchaseDate = '2022-1-12'
  AND PmtAmt = 52
  AND DeliveredOn Is Null
  AND ProductID = 683

The chances of accidentally updating the wrong record in the second version are close to zero.

UPDATE Queries from an Excel Perspective

For those who are transitioning from Excel to Access, it's helpful to draw parallels between the two applications.

In Excel, you might manually change the values in cells or use a formula to update values across a range of cells. You might also copy a block of cells whose values are based on formulas and then paste over that block using "Paste as Values" to overwrite the underlying formulas. The UPDATE query in Access serves a similar purpose, but without all the manual effort.

However, unlike Excel, where you can undo changes easily, changes made by an UPDATE query in Access are not easily reversible. Therefore, it's important to always test your UPDATE query on a development copy of your data before executing it against live production data.

In the next article in this series, we'll explore DELETE queries, which allow you to remove data from your tables. As with UPDATE queries, DELETE queries are powerful tools that should be used with caution. Stay tuned!

Related Advanced Topics

UPDATE [2023-12-01]: Added steps to preview UPDATE query in SELECT view prior to executing (h/t Xavier Batlle).

Acknowledgements

• Initial draft generated with the help of ChatGPT
• One or more code samples generated with the help of ChatGPT