RENAME TABLE

Purpose

You can use this statement to rename one or more tables.

To execute this statement, you must have the ALTER and DROP privileges on the original table and the CREATE and INSERT privileges on the new table.

Limitations and considerations

• When the rename operation is in progress, the system automatically locks the process and prevents other threads from reading or writing the renamed tables until the operation is done.
• If a target table has any active transactions, the rename operation will wait for them to roll back or commit, and will not proceed until then.
• If you use this statement to rename multiple tables, the rename operation proceeds from left to right.
• Before you execute the RENAME TABLE statement, make sure that no target table is locked and no transaction is active on any target table.
• RENAME TABLE is also applicable to views, but you cannot execute this statement to move views across databases.

Syntax

RENAME TABLE table_name TO [new_database_name.]new_table_name
[, table_name2 TO [new_database_name.]new_table_name2   ...];

Parameters

Examples

1. Create tables t1 and t2.

   obclient> CREATE TABLE t1(c1 INT);
   obclient> CREATE TABLE t2(c1 INT);
   

2. Rename table t1 as t11.

   obclient> RENAME TABLE t1 TO t11;
   

3. Rename table t11 as t111 and table t2 as t22.

   obclient> RENAME TABLE t11 TO t111, t2 TO t22;
   

4. Move table t22 to database mysql.

   obclient> RENAME TABLE t22 TO mysql.t22;