Programming instructions

Completing the Trip Maintenance application 141
After the following SQL statement executes:
UPDATE Clients SET LastName = ’Pitt’
WHERE ID = 3
the table contains the following rows:
Update several rows
The UPDATE statement updates all rows that meet the criteria found in the WHERE
clause. If there is no WHERE clause, every row of the table is updated. After the
following SQL statement executes:
UPDATE Clients SET Age = Age + 1
WHERE ID = 3
the table contains the following rows:
Updating multiple records
The cfupdate statement works well when you want to update the current record within
a
cfquery. Alternatively, you can update several rows within a table by issuing a single
query using
cfquery and the SQL UPDATE statement. For example, if the base cost of
all trips increased by 5%, you could issue the following query:
<!-- Routine to increase trip base Cost by 5% -->
<cfquery name="TripQuery" dataSource="CompassTravel">
UPDATE Trips SET baseCost = baseCost * 1.05
</cfquery>
Exercise: using SQL UPDATE with cfquery
In this exercise, you will develop a page to increase the price of every trip by 10%. This
page runs only once and is not part of the Trips Maintenance application. This exercise
shows how to update many database rows using a single SQL UPDATE statement and
the ColdFusion
cfquery tag.
To update multiple database rows using SQL UPDATE with cfquery:
1 In an editor, open a new page and save it as priceincrease.cfm in the my_app
directory.
2 Remove any lines of code that your editor added.
PersonID LastName FirstName Age
1GreenTom 12
2Wall Peter42
3Pitt Jess 20
PersonID LastName FirstName Age
1TomGreen 12
2 Peter Green 42
3PittJess 21