Updated on 2024-08-20 GMT+08:00

RENAME TABLE

Description

In the same statement, changing the name of a single table or multiple tables does not affect the stored data.

Precautions

  • When the name of a single table is changed, this syntax is equivalent to RENAME in ALTER TABLE.
  • Changing the names of multiple tables is equivalent to running ALTER TABLE for multiple times. However, changing the names of local and non-local temporary tables at the same time is not supported.

Syntax

1
RENAME { TABLE | TABLES } table_name TO new_table_name [, table_name2 TO new_table_name2, ...];

If you run this command in a version 5.7 B-compatible database (sql_compatibility set to 'B', b_format_version set to '5.7', and b_format_dev_version set to 's2'), the following situations may occur:

  • If the new table name starts with "#MySQL50#" and is followed by other characters, "#MySQL50#" will be ignored.
  • If the old and new table names are the same, no error is reported.

Parameters

  • TABLE | TABLES

    TABLE and TABLES can be used interchangeably, regardless of the number of tables operated in the statement.

  • table_name TO new_table_name [, table_name2 TO new_table_name2, ...]

    table_name and table_name2 indicate the names of the tables to be modified.

    new_table_name and new_table_name2 indicate the new table names.

    TO is the connection word.

Examples

  • Rename a single table.
    gaussdb=# CREATE TABLE aa(c1 int, c2 int);
    gaussdb=# RENAME TABLE aa TO test_alt1;
    gaussdb=# DROP TABLE test_alt1;
  • Rename multiple tables.
    gaussdb=# CREATE TABLE aa(c1 int, c2 int);
    gaussdb=# CREATE TABLE bb(c1 int, c2 int);
    gaussdb=# RENAME TABLE aa TO test_alt1, bb TO test_alt2;
    gaussdb=# DROP TABLE test_alt1,test_alt2;

Helpful Links

ALTER TABLE