STATEMENT: UPDATE
UPDATE table SET newvalue WHERE criteria
The UPDATE statement is used to
change values in one, or more, or all of the records in a table.
To updating one field:
UPDATE Project
SET ProjectName = 'Grindstone'
WHERE ProjectName = 'Hardwork';
To updating more than one field in a table:
UPDATE Products
SET ShippingWeight = '800lbs', ProductName = 'Grindstone MarkII', Price = '$348.71'
WHERE CatalogNumber = 'GS1097';
Note that UPDATE doesn't generate a result set.
If you want to know which records will be modified, first run a SELECT query
that uses the same criteria.
If the results are satisfactory, then run the update query.
UPDATE Musicians
SET Instrument = 'violin'
WHERE Instrument = 'fiddle'
AND MusicType =
'classical';
The next example updates the
TrainFares table to account for a 10% increase in the cost of a
single ticket on the Edinburgh line:
UPDATE TrainFares
SET Fare = Fare * 1.1
WHERE Line = 'Edinburgh'
AND Journey = 'single';
You can use a "cascade update" operation to update records from
tables that are in a one-to-many relationship with other tables.
A cascade update causes the records in tables that are on
the many side of the relationship to be updated when the
corresponding record in the one side of the relationship is updated.
The Jet database engine will cascade update
if the relationships between tables are configured to enable this option.
While Jet SQL gives us a mechanism to establish a relationship (using the
CONSTRAINT clause) it does not give us any method to configure relationships
to enable cascading operations. However, if a relationship has been
configured to enable cascading operations, for example by using Microsoft
Access or DAO (Data Access Objects) ,
the Jet SQL UPDATE and DELETE) statements will cascade.
Copyright 2000 by Infinite Software Solutions, Inc.
Trademark Information
|