The Update Statement

The UPDATE statement allows you to modify the values in one or more fields of an existing record or records in a table. The basic syntax of the UPDATE statement is the following:

UPDATE table_name

SET columnl = valuel, column2 = value2, ... [WHERE restriction_condition]

Even though the WHERE clause of the UPDATE statement is optional, you must take care to specify it unless you are sure that you don't need it. Executing an UPDATE statement without a WHERE clause will modify the specified field(s) of every record in the specified table. Say, for example, you executed the following statement:

UPDATE Customers

SET [Country/Region] =

= 'USA'

Every record in the Customers table would have its Country field modified to contain the value USA. There are some cases where this mass update capability is useful, but it can also be very dangerous, because there is no way to undo the update if you execute it by mistake.

The more common use of the UPDATE statement is to modify the value of a specific record, identified by the use of the WHERE clause. Before examining an example of this usage, you need to understand a very important aspect of database design called the primary key. The primary key is a field or group of fields in a database table whose values can be used to uniquely identify each record in that table. There is no way to identify a specific record in a table that does not have a primary key. Without that capability, you cannot perform an update on a specific record.

The primary key in this sample Customers table is the ID field. Each customer record in the Customers table has a unique value for ID. In other words, a specific ID value occurs in one, and only one, customer record in the table.

Say that the First Name and Last Name fields have changed for the customer "Company A", whose ID is 1. You could perform an UPDATE to record those changes in the following manner:

UPDATE Customers

SET [First Name] = 'First', [Last Name] = 'Last' WHERE ID = 1

Because you used the primary key field to specify a single record in the Customers table, only this record will be updated.

Was this article helpful?

0 0

Post a comment