MS-Access / Getting Started

---

UPDATE Statement

In the specified table or query, updates the selected columns (either to the value of the given expression or to Null) in all rows that satisfy the search condition. If you do not enter a WHERE clause, all rows in the specified table or query are affected.

Syntax

UPDATE {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  <joined table>},...
[IN <source specification>]
SET {column-name = {<expression> | NULL}},...
[WHERE <search-condition>]
where <joined table> is
({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
  ON <join-specification>)

Notes: If you provide more than one table name, you can update columns only in the table on the many side of a one-to-many relationship. If the tables are related one-to-one, you can update columns in either table. You can also update columns in the table on the one side of a relationship so long as the query returns unique rows for that table. The database must be able to determine the relationship between tables or queries to update columns in a query. In general, if a table is joined by its primary key to a query, you can update columns in the query (because the primary key indicates that the table is on the one side of the join). If you want to update a table with the results of a query, you must insert the query results into a temporary table that can be defined with a one-to-many or one-to-one relationship with the target table and then use the temporary table to update the target.

If you specify a <search-condition>, you can reference only columns found in the target table or query. If you use a subquery in the <search-condition>, you must not reference the target table, the query, or any underlying table of the query in the subquery.

In the SET clause, you cannot specify a column name more than once. You also cannot specify the name of a multi-valued field, a calculated field, or an attachment field. Values assigned to columns must be compatible with the column attributes. If you assign the Null value, the column cannot have the Required property set to Yes.

Both Access and SQL Server let you define column-value constraints (field validation rules in a desktop database), table constraints (table validation rules in a desktop database), and referential integrity checks, so any values that you update must pass these validations or the database will not let you run the query.

Example

To mark contacts who haven't had a contact event since January 1, 2010, enter the following (qxmplSetInactive):

UPDATE tblContacts
 LEFT JOIN
  (SELECT tblContactEvents.ContactID, tblContactEvents.ContactDateTime
   FROM tblContactEvents
   WHERE tblContactEvents.ContactDateTime>=#1/1/2010#) AS Active
 ON tblContacts.ContactID = Active.ContactID
SET tblContacts.Inactive = True
WHERE Active.ContactID IS NULL;

Note: Although the previous query updates rows on the one side of a relationship, the query is valid because the IS NULL test in conjunction with the LEFT JOIN returns exactly one unique row per contact.

See also Expression, IN Clause, Predicates (BETWEEN, Comparison, EXISTS, IN, LIKE NULL, and Quantified), Search-Condition, and WHERE Clause in this tutorial.