Modifying with SQL UPDATE

Modifying with SQL UPDATE

A SQL UPDATE statement is the same as the update query executed by the Access user interface to modify existing data. This statement uses the following syntax:

 UPDATE datasource SET col1 = expression[, col2 = expression, ...] [WHERE condition] 

Given the nature of the statement's purpose, you don't use the GROUP BY, HAVING, or even the ORDER BY clauses with this statement. In addition, UPDATE doesn't return a resultset, but Access displays a message telling you how many records will be updated and asking you to confirm the action. For instance, this simple UPDATE changes all the occurrences of California in the State field in the Clients table to CA:

 UPDATE Clients SET State = "CA" WHERE State = "California" 

TIP

Before updating data via an action query, such as UPDATE, create a copy of the data source. That way, if something goes wrong, you have a copy of the unchanged data. After confirming that the changes are correct, you can delete the copy.

Modifying data won't usually violate referential integrity, unless you're changing a primary or foreign key value, which you shouldn't do anyway. However, changes must respect properties and validation rules in place. When running updates via code, include error handling for these potential errors.