Altering Tables - Learning MySQL and MariaDB - page 75

copy of the table and alters that copy based on the ALTER TABLE statement's instruc-
tions, one clause at a time, from left to right (or top to bottom in our layout). When it's
finished, if there are no errors, it then replaces the original table with the altered tempor-
ary table — much like we're doing here, but rapidly and behind the scenes.
If there are errors in processing any clause of the ALTER TABLE statement, it just deletes
the temporary table and leaves the original table unchanged, and then returns an error
message to the client. So in the previous example, in the temporary table that MySQL cre-
ates, it first added the column body_id . Once that was done, it then added the bill_id
column and put it after the body_id column in that temporary table. Your tendency
might have been to have entered AFTER wing_id at the end of each of the ADD
COLUMN clauses. That would have worked, but the columns would have been in reverse
order (i.e., wing_id , endangered , bill_id , body_id ). So if we want body_id
to be located after wing_id , and bill_id to be located after body_id , and so on, we
have to say so in the SQL statement as shown.
Let's change now the value of the endangered column. The table only has five rows in
it at the moment and none of the birds they represent are endangered. Still, let's set the
value of the endangered column to 0 for four of them. To do this, weuse the UPDATE
statement (you'll learn more about it in Chapter8 , so don't worry if this is unfamiliar):
UPDATE birds_new SET endangered = 0
WHERE bird_id IN ( 1 , 2 , 4 , 5 );
This will set the value of the endangered column to 0, or rather unset it, for the rows in
which the bird_id column has one of the values listed within the parentheses. Basic-
ally, we'll change four rows of data, but leave the one unchanged where bird_id equals
3. Remember that when we created the endangered column, we gave a default of
b'1' , meaning the bit is set by default. The preceding statement is unsetting that column
for the four rows identified in the WHERE clause.
Now we'll retrieve data usingthe SELECT statement (covered in Chapters 3 and 7 ), based
on whether the endangered column is set. Because the birds_new table is now
wider, we'll enter the following SQL statement using the \G for an easier-to-read display:
SELECT bird_id, scientific_name, common_name
FROM birds_new
WHERE endangered \G
*************************** 1. row ***************************
bird_id: 3
scientific_name: Aix sponsa