UPDATE Statement (Microsoft Access SQL)

Office 2013 and later

Last modified: March 09, 2015

Applies to: Access 2013 | Office 2013

In this article
Syntax
Remarks
Example

Creates an update query that changes values in fields in a specified table based on specified criteria.

UPDATE table SET newvalue WHERE criteria;

The UPDATE statement has these parts:

Part

Description

table

The name of the table containing the data you want to modify.

newvalue

An expression that determines the value to be inserted into a particular field in the updated records.

criteria

An expression that determines which records will be updated. Only records that satisfy the expression are updated.

UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables.

You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom:

SQL
Copy 
UPDATE Orders 
SET OrderAmount = OrderAmount * 1.1, 
Freight = Freight * 1.03 
WHERE ShipCountry = 'UK';
Important noteImportant

  • UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation. If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.

  • Maintain backup copies of your data at all times. If you update the wrong records, you can retrieve them from your backup copies.

This example changes values in the ReportsTo field to 5 for all employee records that currently have ReportsTo values of 2.

VBA
Copy 
Sub UpdateX() 
 
    Dim dbs As Database 
    Dim qdf As QueryDef 
 
    ' Modify this line to include the path to Northwind 
    ' on your computer. 
    Set dbs = OpenDatabase("Northwind.mdb") 
     
    ' Change values in the ReportsTo field to 5 for all  
    ' employee records that currently have ReportsTo  
    ' values of 2. 
    dbs.Execute "UPDATE Employees " _ 
        & "SET ReportsTo = 5 " _ 
        & "WHERE ReportsTo = 2;" 
         
    dbs.Close 
 
End Sub
Show: