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.

Considerations

• The rename operation proceeds automatically. When the rename operation is in progress, other threads cannot read any tables.
• 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 table is locked and no transaction is active.
• RENAME TABLE is also applicable to views, but cannot 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;