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 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information