Every record in the Customerstable would have its Countryfield 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 UPDATEstatement is to modify the value of a specific record, identified by
the use of the WHEREclause. Before examining an example of this usage, you need to understand a very
important aspect of database design called theprimary 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 Customerstable is the IDfield. Each customer record in the Customers
table has a unique value for ID. In other words, a specific IDvalue occurs in one, and only one, customer
record in the table.
Say that the FirstNameand LastNamefields have changed for the customer “Company A”, whose ID
is 1. You could perform an UPDATEto record those changes in the following manner:
SET [First Name] = ‘First’, [Last Name] = ‘Last’
WHERE ID = 1
Because you used the primary key field to specify a single record in the Customerstable, only this
record will be updated.
The DELETE Statement
The DELETEstatement allows you to remove one or more records from a table. The basic syntax of the
DELETEstatement is the following:
DELETE FROM table_name
As with the UPDATEstatement, notice that the WHEREclause is optional. This is probably more dangerous
in the case of the DELETEstatement, however, because executing a DELETEstatement without a WHERE
clause will delete every single record in the specified table. Once again, there is no way to undo this, so be
very careful. You should always include a WHEREclause in your DELETEstatements unless you have
some very specific reason for wanting to remove all records from a table.
Assume that, for some reason, an entry was made into the Customerstable with the IDvalue of 30 by
mistake (maybe they were a supplier rather than a customer). To remove this record from the
Customerstable, you would use the following DELETEstatement:
DELETE FROM Customers
WHERE ID = 30
Once again, because you used the record’s primary key in the WHEREclause, only that specific record will
be affected by the DELETEstatement.
Chapter 20: Data Access with ADO