UPDATE Statement

Description

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

Syntax

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.

Remarks 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:

UPDATE Orders
SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK';

Important

UPDATE doesn't generate a result set. Also, after you update records using an update query, you can't 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.

See Also

SELECT statement, WHERE clause.

Specifics (Microsoft Access)

Since the UPDATE statement doesn't generate a result set, there is no query datasheet for Microsoft Access to display when you use this statement.

If you want to confirm each change, you can use the Replace command on the Edit menu of the form or datasheet rather than an update query.

Example

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

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

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Warning These examples makes changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

The following example changes values in the ReportsTo field to 5 for all employee records that currently have ReportsTo values of 2:

UPDATE Employees SET ReportsTo = 5 WHERE ReportsTo = 2;

The next example increases the UnitPrice for all non-discontinued products from supplier #8 by 10 percent:

UPDATE Products SET UnitPrice = UnitPrice * 1.1
WHERE SupplierID = 8 AND Discontinued = No;

The following example reduces the UnitPrice for all non-discontinued products supplied by Tokyo Traders by 5 percent. The Products and Suppliers tables have a many-to-one relationship.

UPDATE Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID
SET UnitPrice = UnitPrice * .95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;