RENAME TABLE statement

Use the RENAME TABLE statement to change the name of a table. The RENAME TABLE statement is an extension to the ANSI/ISO standard for SQL.

Syntax


Element	Description	Restrictions	Syntax
`new_table`	New name for `old_table`	Must be unique among the names of sequences, tables, views, and synonyms in the database	Identifier
`old_table`	Name that `new_table` replaces	Must be the name (not the synonym) of a table that is registered in the current database	Identifier
`owner`	Current® owner of the table	Must be the owner of the table.	Owner name

Usage

To rename a table, you must be the owner of the table, or have the ALTER privilege on the table, or have the DBA privilege on the database.

An error occurs if old_table is a synonym, rather than the name of a table.

The old_table can be an object that the CREATE EXTERNAL TABLE statement defined.

The renamed table remains in the current database. You cannot use the RENAME TABLE statement to move a table from the current database to another database, nor to rename a table that resides in another database.

You cannot change the table owner by renaming the table. An error occurs if you try to specify an owner. qualifier for the new name of the table.

When the table owner is changed, you must specify both the old owner and new owner.

In an ANSI-compliant database, if you are not the owner of old_table, you must specify owner.old_table as the old name of the table.

If old_table is referenced by a view in the current database, the view definition is updated in the sysviews system catalog table to reflect the new table name. For further information on the sysviews system catalog table, see the HCL OneDB™ Guide to SQL: Reference.

If old_table is a triggering table, the database server takes these actions:

Replaces the name of the table in the trigger definition but does not replace the table name where it appears inside any triggered actions
Returns an error if the new table name is the same as a correlation name in the REFERENCING clause of the trigger definition

When the trigger executes, the database server returns an error if it encounters a table name for which no table exists.

Using RENAME TABLE to Reorganize a Table

The RENAME TABLE statement can be a useful alternative to the ALTER TABLE statement when you need to reorganize the schema of an existing table. Suppose, for example, that you decide to change the order of columns in the items table of the stores demonstration database. You can reorganize the items table to move the quantity column from the fifth position to the third position by following these steps:

Create a new table, new_table, that contains the column quantity in the third position.
Fill the table with data from the current items table.
Drop the old items table.
Rename new_table with the identifier items.

The following example uses the RENAME TABLE statement as the last step:

CREATE TABLE new_table
   (
   item_num         SMALLINT,
   order_num        INTEGER,
   quantity         SMALLINT,
   stock_num        SMALLINT,
   manu_code        CHAR(3),
   total_price      MONEY(8)
   );
INSERT INTO new_table
   SELECT item_num, order_num, quantity, stock_num,
        manu_code, total_price FROM items;
DROP TABLE items;
RENAME TABLE new_table TO items;