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

time, all the more reason why you should endeavour to create the table accurately in the
first instance.
Many of these commands are also a MySQL addition to the original ANSI SQL92 defini-
tion, so may not work on other database management systems that are only compliant with
this standard.

UPDATE Revisited

I previously warned of the dangers of doing an unrestricted update, that is, an UPDATE
without the WHERE clause. However, I will now show you a time when such an update is
useful. When we were adding columns to our visitorbook table we added a score column. It
would be useful to add some data to that column for use later on. We could just set all of the
columns to the same value, but instead we will use the data stored in one column to 'seed'
the data in the score column. Again, we could just copy from one column to the next but we
will change it slightly by multiplying the source column to get the new one. This sounds
complex, so run the following script:

UPDATE visitorbook
SET score=entryID * 2

For each row, this query will take the value of the entryID field, multiply it by 2, and store
it in the score field of that same row. To view the results, run the following:

SELECT entryID, score
FROM visitorbook

Figure 8.10 shows the results of our update.
Looking at Figure 8.10, you can see that we now have a different score in each row of the
table, and each score is twice the value of the entryID . We just used the entryID as a chang-
ing number to seed the score column, to show that an unrestrained UPDATE does not nec-
essarily have to result in all the updated fields having the same value.

Figure 8.10 UPDATE calculating values for each row.