Java Reference
In-Depth Information
A frequent requirement in database applications is the need to update records. For
example, when a contact moves, you need to change his or her address. The way to
do this is with the SQL UPDATE statement, using a WHERE clause to identify the
record you want to change. Here's an example:
UPDATE Customers
SET Street = '55 Broadway', ZIP = '10006'
WHERE First_Name = 'Michael' AND Last_Name = 'Corleone';
This statement first evaluates the WHERE clause to find all records with matching
First_Name and Last_Name. It then makes the address change to all of those
records.
If you omit the WHERE clause from the UPDATE statement, all records
in the given table are updated.
Caution
Using calculated values with UPDATE
You can use the UPDATE statement to update columns with calculated values. For
example, if you add stock to your inventory, instead of setting the Qty column to an
absolute value, you can simply add the appropriate number of units with a calculated
UPDATE statement like the following:
UPDATE Inventory
SET Qty = QTY + 24
WHERE Name = 'Corn Flakes';
When you use a calculated UPDATE statement like this, you need to make sure that
you observe the rules for INSERTS and UPDATES mentioned earlier. In particular,
ensure that the data type of the calculated value is the same as the data type of the
field you are modifying, as well as being short enough to fit in the field.
Using Triggers to Validate UPDATES
In addition to defining constraints, the SQL language allows you to specify security
rules that are applied when specified operations are performed on a table. These
rules are known as
triggers
, as they are triggered automatically by the occurrence of
a database event such as updating a table.
A typical use of a trigger might be to check the validity of an update to an inventory
table. The following code snippet shows a trigger that automatically rolls back or
voids an attempt to increase the cost of an item in inventory by more than 15 percent.
CREATE TRIGGER FifteenPctRule ON INVENTORY FOR INSERT, UPDATE AS
DECLARE @NewCost money
DECLARE @OldCost money
