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

Figure 8.3 The damage caused by omitting the WHERE.

Thankfully, our example database only has six rows, only one of which had some mean-
ingful data, so we will not have to do much work to fix this problem. Imagine, however, the
amount of work that would be involved if we had used a similar query on a commercial
website with hundreds or thousands of pages! This example shows that it is best to get into
the habit of always using a WHERE clause with the UPDATE query. Try to think of the
query as UPDATE , SET , WHERE as opposed to UPDATE , SET and it may save you a lot of
work. I hope this is the last time you will see this happen and you will not make this mis-
take when it matters!
To fix this, we will first clear out the wrong data by using the UPDATE without the
WHERE for one last time:

UPDATE webpage
SET

content = “”

The above query sets all of the cells in the content column to the empty string. Now we
will re-run the UPDATE query for row 6 but this time use the WHERE clause:

UPDATE webpage
SET content = “My email is [e-mail]”

WHERE ID = 6

After running the query above, select everything from the webpage table and you should
have the more satisfactory results as shown in Figure 8.4. We have still lost the data that was
in row 2 but we will remedy that next. Notice that by using the WHERE clause to match the
primary key field ( ID ) of the table we were able to restrict our update to exactly the row that
we wished to change.
Now that we have successfully changed the data in one column, we can add some content
to all of the rows in the webpage table one by one. The following script will do this for you: