Databases Reference
In-Depth Information
UPDATE SALESPERSON SET SPNAME = 'WATSON'
WHERE SPNAME = 'Smythe'
This time, you would change “Smythe” to “Watson” for every salesperson with
the name “Smythe”. The point here is that you must be careful when defining
your search conditions when updating (or, as you will see later, deleting) table
information.
Here's another example that requires a logical operator in the WHERE clause.
Every salesperson that currently has a COMMPERCT value of 10 hired in 2004
or later is getting an automatic increase to 11.5. To do this, you could run:
UPDATE SALESPERSON SET COMMPERCT = 11.5
WHERE COMMPERCT = 10 AND YEARHIRE >= 2004
Now, let's say you changed your mind. You decide to put any salespersons
hired in or after 2006 back on the default percentage. To do this, run
UPDATE SALESPERSON SET COMMPERCT = DEFAULT
WHERE YEARHIRE >= 2006
Just as an added note, this last example could have also been run as the fol-
lowing with the same result:
UPDATE SALESPERSON SET COMMPERCT = DEFAULT
WHERE YEARHIRE > 2005
The difference in the search condition logic is less complicated than in the pre-
vious example. If processing a very large number of salespersons, there could be
a difference in performance between the two, with the latter running faster.
6.4.3 Using DELETE
DELETE provides a way to get rid of the information that is no longer needed.
Deleting database rows is usually necessary when the entity these rows corre-
spond to becomes irrelevant or completely disappears from the real world. For
example, an employee quits, a customer does not place orders any more, or ship-
ment information is no longer needed. Perhaps order information, once it reaches
a certain age, is transferred to an archive table. However, there isn't a TRANSFER
or ARCHIVE command. Instead, you would run INSERT to write the orders into
the archive table, and then DELETE to remove the orders from the original table,
effectively transferring them.
The ANSI SQL-99 syntax for this statement, as you might guess, is relatively
simple:
DELETE [FROM]
table_or_view_name
[WHERE
search_condition
]
