Updating and Deleting Data - Learning MySQL and MariaDB - page 164

Looking at the enumerated values of this column, we decide that the choices seem some-
what sexist to us. We have one choice for boys and men, regardless of their age and marit-
al status, and three choices for women. We also don't have other genderless choices like
Dr. , but we decide to ignore those possibilities for now. In fact, we could eliminate the
column so as not to be gender biased, but we decide to wait before making that decision.
At this point, we want to change our schema so it limits the list of choices in the column
to Mr. or Ms. however, we should not make that change to the schema until we fix all the
existing values in the column. To do that, we'll enter this UPDATE statement:
UPDATE humans
SET formal_title = 'Ms.'
WHERE formal_title IN ( 'Miss' , 'Mrs.' );
Now that all of the members have either a value of Mr. or Ms. in the formal_title
column, we can change the settings of that column to eliminate the other choices. We'll
use the ALTER TABLE statement covered in Chapter4 . Enter the following to change the
table on your server:
ALTER TABLE humans
CHANGE COLUMN formal_title formal_title ENUM('Mr.','Ms.');
Query OK, 62 rows affected (0.13 sec)
Records: 62 Duplicates: 0 Warnings: 0
As you can see from the message in the results, the column change went well. However, if
we had forgotten to change the data for one of the rows (e.g., didn't change Miss to Ms.
for one person), the Warnings would show a value of 1. In that case, you would then have
to execute the SHOW WARNINGS statement to seethis warning:
SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'formal_title' at row 44
This tells us that MySQL eliminated the value for the formal_title column of the
44th row. We'd then have to use the UPDATE statement to try to set the formal_title
for the person whose title was clobbered and hope we set the title correctly. That's why
it's usually better to update the data before altering the table.
Sometimes, when changing bulk data, you have to alter the table before you can do the
update. For example, suppose that we decide that we prefer to have the enumerated values
of the formal_title set to Mr or Ms , without any periods. To do this, we would need