Changing Data - Database Design Manual: Using MySQL for Windows - page 85

8

Changing Data

Altering Data

Once you have populated your tables with data, that is seldom the end of the process.
Although most websites accumulate much data that does not change over time, such as
page access logs and cookie tables, other data changes. On a website that allows users to log
on, for example, users may wish to change their password or their email address. On a data-
base-driven website, changes to the contents of the pages will require changes to the under-
lying database that contains the pages.
SQL therefore has various commands that allow you to change the data that you have
previously stored in your tables. This chapter contains some common commands that are
used for this purpose:

•

UPDATE allows you to change the contents of data within a table.

•

ALTER allows you to make changes to the table structure once you have created it.

•

REPLACE prevents errors when creating things that may already exist.

We will now describe these commands.

UPDATE
UPDATE allows you to change the contents of parts of a table without altering its structure.
The basic format of the UPDATE command is as follows:

UPDATE tablename
SET columnname=numericvalue,
columnname=”textvalue”
WHERE condition

As you work through the topic you might have found it frustrating that our webpage
table contains hardly any content, just the name of each page. Select everything from the
webpage table to remind yourself of its limited contents. Figure 8.1 shows the table.
You will notice from Figure 8.1 that the content column is largely filled with NULLs. We
will use the UPDATE keyword to remedy this and give it some more relevant data. The fol-
lowing script will add some text to the content column:

87