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 ...
Get Programming the Perl DBI now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.