ASP.NET Tutorial

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

Figure B-5. General format for UPDATE statement.

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'

Listing B-26. Updating a record in a database table.

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

It is nearly always necessary to include a WHERE clause in an UPDATE statement in order to change a particular record. If no WHERE clause appears, then all records in the table are updated with the same values.

A common way of specifying a record to change is by matching its unique "key" field.

UPDATE Products
  SET ItemQuantity = 0 
  WHERE ProductID = 'AA111'

Listing B-27. Updating a record based on the value of a key field.

You can, though, use any of the common conditional operators given previously in Figure B-2.

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

Listing B-28. Updating a record based on multiple criteria.

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 and INSERT statements, the UPDATE statement normally is composed in a script using combinations of literal strings and variables.

Dim TheQuantity As Integer
Dim TheType as String
...
SQLString = "UPDATE Products " & _
            "SET ItemQty = 0 " & _
            "WHERE ItemQuantity < " & TheQuantity & _
            " AND NOT ItemType = '" & TheType & "'"

Listing B-29. A script-composed UPDATE statement to update a record based on multiple criteria.

This code resolves into a UPDATE statement resembling the following.

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

Listing B-30. An UPDATE statement composed in script.