DevGuru Jet SQL UPDATE Statement

Quick References

Features

Partners


			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.