Altering Tables - Learning MySQL and MariaDB - page 90

altered table in the rookery database first. Renaming the table that's being replaced is a
safer choice, so we'll go with that option.
Let's rename the birds table in the rookery database to birds_old and then re-
name and relocate the birds_new table from the test database to birds in the
rookery database. To do all of this in one SQL statement, enter the following:
RENAME TABLE rookery . birds TO rookery . birds_old ,
test . birds_new TO rookery . birds ;
If there was a problem in doing any of these changes, an error message would be gener-
ated and none of the changes would be made. If all of it went well, though, we should
have two tables in the rookery database that are designed to hold data on birds.
Let's run the SHOW TABLES statement to see the tables in the rookery database. We'll
request only tables starting with the word birds by using the LIKE clausewith thewild-
card, % . Enter the following in mysql :
SHOW TABLES IN rookery LIKE 'birds%';
+----------------------------+
| Tables_in_rookery (birds%) |
+----------------------------+
| birds |
| birds_bill_shapes |
| birds_body_shapes |
| birds_details |
| birds_new |
| birds_old |
| birds_wing_shapes |
+----------------------------+
The birds table used to be the birds_new table that we altered in the test database.
The original birds table has been renamed to birds_old . The other tables in the res-
ults set here are the ones we created earlier in this chapter. Because their names start with
birds , they're in the results. After running a SELECT statement to ensure that you haven't
lost any data, you might want to delete the birds_old table. You would delete the
birds_old tablewith the DROP TABLE statement in mysql . It would look like the fol-
lowing, but don'tenter this:
DROP TABLE birds_old ;