Programming the Perl DBI

Updating Data

The final way in which modifications can be performed on data stored within tables in a database is to make in-place modifications of existing data by updating the values of particular columns in particular rows. With an UPDATE statement, rows are neither inserted nor deleted, and the structure of the table itself is not altered.

UPDATE statements are extremely powerful, in that it is possible to update multiple rows of data in one statement. If desired, the new values may be supplied by the returned values from a SELECT statement following the syntax of the INSERT command.

The most simple and useful UPDATE is to update a column of a single row within a table to a new value. For example, if you wanted to update the location of the “Avebury” row within the megaliths table, the following SQL statement would work:

UPDATE megaliths
SET location = 'Near Devizes, Wiltshire'
WHERE name = 'Avebury'

You should notice the condition clause specified in this statement. If the statement did not check for the exact name of the site, every row within the table would have had the UPDATE statement performed against it, causing a potentially disastrous data corruption. Condition clauses may be specified in exactly the same way as used in other SQL commands such as DELETE and SELECT.

UPDATE statements may also update more than one column in a single statement, by simply listing the columns we wish to update in a comma-separated list. For example, to update both the name and description ...