Database Reference
In-Depth Information
The SQL UPDATE Statement
The
SQL UPDATE statement
is used to change values of existing rows. For example, the fol-
lowing statement will change the value of City to 'New York City' for the View Ridge Gallery
customer whose CustomerID is 1000 ( Jeffrey Janes):
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-01 *** */
UPDATE
CUSTOMER
SET
City = 'New York City'
WHERE
CustomerID = 1000;
To change the value of both City and State, we would use the SQL statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-02 *** */
UPDATE
CUSTOMER
SET
City = 'New York City', State = 'NY'
WHERE
CustomerID = 1000;
The DBMS will enforce all referential integrity constraints when processing UPDATE
commands. For the View Ridge Gallery database, all keys are surrogate keys, but for tables
with data keys, the DBMS will cascade or disallow (NO ACTION) updates according to the
specification in the FOREIGN KEY constraint. Also, if there is a FOREIGN KEY constraint, the
DBMS will enforce the referential integrity constraint on updates to a foreign key.
Bulk Updates
It is quite easy to make bulk updates with the UPDATE statement. It is so easy, in fact, that it is
dangerous. Consider the SQL UPDATE statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-03 *** */
UPDATE
CUSTOMER
SET
City = 'New York City';
This statement will change the value of City for every row of the CUSTOMER table. If we had
intended to change just the value for customer 1000, we would have an unhappy result—every
customer would have the value 'New York City'.
You can also perform bulk updates using an SQL WHERE clause that finds multiple rows.
If, for example, we wanted to change the AreaCode for every customer who lives in Denver, we
would code:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-04 *** */
UPDATE
CUSTOMER
SET
AreaCode = '303'
WHERE
City = 'Denver';
Search WWH ::
Custom Search