UPDATE Statement

The UPDATE statement is used to change records in a database table. Its general format for updating a single record is shown below:

UPDATE TableName
     SET (FieldName1=value1 [,FieldName2=value2]...)
     WHERE criteria

The keyword UPDATE is followed by the name of the table being updated. The keyword SET is followed by a comma-separated list of field names and associated data values that change the current values of the specified record in the table. The WHERE clause gives the criteria for locating the particular record to update. It is not necessary to change the values of all fields in the record; specify only those fields and values to be changed.

UPDATE MyTable
  SET Field2 = 'new text value', Field3 = 200, Field5 = #02/02/04#
  WHERE Field1 = 'KEY001'

In this example, three fields are changed in the record identified by the value 'KEY001' in Field1 in table MyTable. Values for fields that are defined as text fields in the table must be enclosed in single quotes (apostrophes); values for numeric fields are not enclosed in single quotes; values for date/time fields are enclosed in # symbols.

The WHERE Clause

The keyword WHERE is followed by one or more selection criteria. A common way of specifying a record to change is by matching its unique "key" field.

You can, though, use any of the common conditional operators,

= (equal to)
<> (not equal to)
< (less than)
> (greater than)
<= (less than or equal to)
=> (equal to or greater than)

to identify the record. Plus, you can combine tests using the logical operators AND, OR, and NOT to expand or contract your selection:

UPDATE Products
  SET ItemQuantity = 0
  WHERE ItemQuantity < 10 AND NOT ItemType = 'Software'

In this case, more that one record may be updated. If the WHERE clause is not included in the statement, all records in the table have the specified fields changed.

If a text field contains apostrophes, they must be replaced with double apostrophes. See the discussion about apostrophes in data values relative to the SELECT statement.

As in the case of SELECT, INSERT, and DELETE statements, the UPDATE statement normally is composed in a script using combinations of literal strings and variables.


SQLString = "UPDATE Products SET ItemQty = 0 WHERE ItemQuantity < $TheQuantity AND NOT ItemType = '$TheType &'"

This code resolves into a UPDATE statement resembling the following: 

SQLString = "UPDATE Products SET ItemQty = 0 WHERE ItemQuantity < 10 AND NOT ItemType = 'Software'"