Database Reference
In-Depth Information
to add that pair of choices to the
ENUM
column before we eliminate the old values. Then
we can easily change the data to the new values. In this situation, we can tweak the criter-
ia of the
WHERE
clause of the
UPDATE
statement. The values have a pattern: the new val-
ues are the same as the first two characters of the old value. So we can use a function to
extract that part of the string. We would do something like this:
ALTER TABLE
humans
CHANGE COLUMN
formal_title formal_title
ENUM
(
'Mr.'
,
'Ms.'
,
'Mr'
,
'Ms'
);
UPDATE
humans
SET
formal_title
=
SUBSTRING
(
formal_title
,
1
,
2
);
ALTER TABLE
humans
CHANGE COLUMN
formal_title formal_title
ENUM
(
'Mr'
,
'Ms'
);
The first
ALTER TABLE
statement adds the two new choices of titles without a period to
the column, without yet eliminating the previous two choices because existing table con-
tents use them. The final
ALTER TABLE
statement removes the two old choices of titles
with a period from the column. Those two SQL statements are fine and not very interest-
ing. The second one is more interesting, the
UPDATE
.
In the
SET
clause, we set the value of the
formal_title
column to a substring of its
current value. We're usingthe
SUBSTRING()
function to extract the text. Within the
parentheses, we give the column from which to get a string (
formal_title
). Then we
give the start of the substring we want to extract: 1, meaning the first character of the ori-
ginal string. We specify the number of characters we want to extract: 2. So wherever
SUBSTRING()
encounters “Mr.” it will extract “Mr”, and wherever it encounters “Ms.”
it will extract “Ms”.
It's critical to note that fuctions don't
change
the data in the table.
SUBSTRING()
simply
gives you back the substring. In order to actually change the column, you need the
SET
formal_title =
clause. That changes
formal_title
to the value you got back
from
SUBSTRING()
. Note that, if you wanted, you could just as easily have run
SUBSTRING()
on one column and used it to set the value of a different one.
In this chapter, we'll work with a few string functions that are useful with the
UPDATE
statement. We'll cover many morestring functions in
Chapter10
.
Limiting Updates
As mentionednear the beginning of this chapter,
UPDATE
can be a powerful tool for
quickly changing large amounts of data in a MySQL database. As a result, you should al-
