The UPDATE Query

You can use the UPDATE query to specify a set of records from one or more tables and assign new values to one or more fields in that result set. The new value can be either a literal value assigned to each record to be updated, or an expression that uses existing values as part of the new value.

The following example updates the Salary field for all sales managers:

UPDATE Employees 
SET Employees.Salary = 50000
WHERE Employees.Title = 'Sales Manager';

Every employee record that meets the selection criterion is assigned the same value of 50,000.

The next example applies a formula that increases the employees’ salaries by 10 percent for all employees who aren’t sales managers:

UPDATE Employees 
SET Employees.Salary = Employees.Salary*1.1
WHERE Employees.Title <> 'Sales Manager';

The Salary field value for each record in the result set produced by applying the WHERE clause is multiplied by the constant value 1.1 and then stored in the same Salary field.

Multiple-Table UPDATE Queries

In addition to simple single-table updates, with Microsoft Jet you can create an UPDATE query that’s the result of a multiple-table join. The following example joins the Orders and Order Details tables. It uses a selection criterion from the Orders table, but updates values in the Order Details table:

UPDATE Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID 
SET [Order Details].Discount = 0.25
WHERE Orders.CustomerID = 'FRANS';

In this case, the fields in the table on the “many” side of a one-to-many relationship are being updated. Fields on the “one” side of the join can also be updated, with certain limitations:

UPDATE Orders 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID 
SET [Order Details].Discount = 0.25, 
	Orders.OrderDate = #6/16/95#
WHERE Orders.CustomerID = 'FRANS';

You can’t create a multiple-table UPDATE query if one of the tables in the join is a summary query — a query that uses the GROUP BY clause, for example.

See Also For more information about UPDATE queries, see the “Selecting Unique Records” section earlier in this chapter.