Name
RENAME TABLE
Synopsis
RENAME TABLEtable
TOtable
[,...]
Use this statement to rename a given
table to the name given after the TO
keyword.
Additional tables may be specified for renaming in a comma-separated
list. Multiple renames are performed left to right, and if any errors
are encountered, all of the table name changes are reversed from
right to left. While tables are being renamed, no other client can
interact with the tables involved. Tables that are currently locked
or tables that are part of a transaction in progress cannot be
renamed.
Tables can be renamed and moved to databases on the same filesystem. As an example, suppose that users add data to a particular table during the course of the day and that each day the contents of the table are to be preserved. Suppose further that you want to reset the table back to no data. Here’s how to do that:
CREATE TABLE survey_new LIKE survey_bak; RENAME TABLE survey TO survey_bak, survey_new TO survey;
In this example, a new table called survey_new is created based on the table structure of the old table called survey, but without the data. In the second SQL statement, the old table is renamed to survey_bak and the blank table, survey_new, is renamed to survey. If issued from a program, the new name could be generated based upon the date so that each day’s data could be preserved.
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.