00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Rename Table

Last updated: 5 min read

Table names chosen early in a project don't always age well: emp might need to become employees. MySQL renames tables instantly with RENAME TABLE or ALTER TABLE … RENAME, because only metadata changes and no rows are copied. This tutorial covers both statements, atomic swaps, moving tables between databases, and the side effects to watch for.

1. Syntax

syntax.sql 
RENAME TABLE old_name TO new_name [, old_name2 TO new_name2 ...];

ALTER TABLE old_name RENAME [TO | AS] new_name;
RENAME TABLEALTER TABLE … RENAME
Several tables in one statementYes, atomicallyNo, one table
Combine with other changesNoYes, e.g. add a column in the same statement
Works on temporary tablesYes (8.0.13+)Yes
Privileges neededALTER and DROP on old, CREATE and INSERT on newSame

2. Renaming a Table

rename.sql 
RENAME TABLE emp TO employees;
SHOW TABLES;

Result: 3 rows

Tables_in_hrms
dept
employees
sales_team

The same with ALTER TABLE:

alter-rename.sql 
ALTER TABLE dept RENAME TO departments;
SHOW TABLES;

Result: 3 rows

Tables_in_hrms
departments
employees
sales_team

3. What Moves with the Table and What Doesn't

ItemAfter rename
Rows, columns, indexesUnchanged
Foreign keys on the table and pointing to itUpdated automatically
TriggersStay attached to the table
Views that use the old nameBroken: they still mention the old name
Stored procedures, functions, eventsNot updated: SQL inside them still uses the old name
Table-level privileges (GRANT … ON db.old)Not moved: grant again on the new name

The foreign key now references departments:

query.sql 
SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'hrms';

Result: 1 row

CONSTRAINT_NAMETABLE_NAMEREFERENCED_TABLE_NAME
fk_emp_deptemployeesdepartments

But the view is broken because it still says emp:

query.sql 
SELECT * FROM sales_team;
Output 
ERROR 1356 (HY000): View 'hrms.sales_team' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Recreate it with the new name:

fix-view.sql 
CREATE OR REPLACE VIEW sales_team AS SELECT name FROM employees WHERE dept_id = 1;
SELECT * FROM sales_team;

Result: 2 rows

name
Arun
Chandu

And the table-level grant still names the old table:

query.sql 
SHOW GRANTS FOR 'hr_app'@'localhost';

Result: 2 rows

Grants for hr_app@localhost
GRANT USAGE ON *.* TO `hr_app`@`localhost`
GRANT SELECT ON `hrms`.`emp` TO `hr_app`@`localhost`
fix-grants.sql 
REVOKE SELECT ON hrms.emp FROM 'hr_app'@'localhost';
GRANT  SELECT ON hrms.employees TO 'hr_app'@'localhost';
SHOW GRANTS FOR 'hr_app'@'localhost';

Result: 2 rows

Grants for hr_app@localhost
GRANT USAGE ON *.* TO `hr_app`@`localhost`
GRANT SELECT ON `hrms`.`employees` TO `hr_app`@`localhost`

4. Renaming Several Tables at Once

All renames in one RENAME TABLE happen together; if one fails, none happen:

multi.sql 
RENAME TABLE employees TO staff, departments TO teams;
SHOW TABLES;

Result: 3 rows

Tables_in_hrms
sales_team
staff
teams
chain.sql 
RENAME TABLE staff TO employees, teams TO staff;
SHOW TABLES;

Result: 3 rows

Tables_in_hrms
employees
sales_team
staff

Renames run left to right, so teams TO staff works: by then the old staff has already become employees. If any step fails, the whole statement fails and nothing changes. Here the last step refers to employees, which the first step already renamed:

query.sql 
RENAME TABLE employees TO people, staff TO employees_typo, employees TO x;
Output 
ERROR 1146 (42S02): Table 'hrms.employees' doesn't exist
query.sql 
SHOW TABLES;

Nothing was renamed

Tables_in_hrms
employees
sales_team
staff

5. Swapping a Table with a New Version

Atomic table swap with RENAME TABLEBefore: prices holds old data and prices_new holds freshly loaded data. One RENAME TABLE statement renames prices to prices_old and prices_new to prices at the same moment, so applications never see a missing table.Beforepricesold data, in useprices_newfresh data, loadedRENAME TABLEprices TO prices_old,prices_new TO prices;After (one atomic step)pricesfresh data, in useprices_oldkept for rollbackApplications keep querying 'prices' and never see an error
Figure 1: renaming several tables in one statement is atomic, which makes it perfect for swapping in new data.
swap.sql 
CREATE TABLE prices (sku VARCHAR(10) PRIMARY KEY, price DECIMAL(8,2));
INSERT INTO prices VALUES ('A1', 100), ('B2', 250);

-- load the new price list into a separate table
CREATE TABLE prices_new LIKE prices;
INSERT INTO prices_new VALUES ('A1', 110), ('B2', 240), ('C3', 99);

-- swap in one atomic step
RENAME TABLE prices TO prices_old, prices_new TO prices;

SELECT * FROM prices;

Result: 3 rows

skuprice
A1110.00
B2240.00
C399.00

6. Moving a Table to Another Database

Qualify the new name with a database to move the table there (same server only):

move.sql 
CREATE DATABASE archive;
RENAME TABLE hrms.prices_old TO archive.prices_2025;
SHOW TABLES FROM archive;

Result: 1 row

Tables_in_archive
prices_2025
A table with triggers can't be moved to a different database (error 1435). Drop and recreate the triggers around the move.

7. Common Errors

query.sql 
RENAME TABLE employees TO staff;
Output 
ERROR 1050 (42S01): Table 'staff' already exists
query.sql 
RENAME TABLE missing_table TO something;
Output 
ERROR 1146 (42S02): Table 'hrms.missing_table' doesn't exist
ErrorCause
1050: Table '…' already existsThe new name is taken
1146: Table '…' doesn't existTypo in the old name or wrong database
Statement waitsAnother session has an open transaction on the table (metadata lock)

8. Practice Exercises

Exercise 1: Rename employees to team_members using ALTER TABLE and confirm.
solution.sql 
ALTER TABLE employees RENAME TO team_members;
SHOW TABLES LIKE 'team%';

Result: 1 row

Tables_in_hrms (team%)
team_members
Exercise 2: Swap the names of staff and team_members in one statement.
solution.sql 
RENAME TABLE staff TO tmp_swap, team_members TO staff, tmp_swap TO team_members;
SHOW TABLES;

Result: 4 rows

Tables_in_hrms
prices
sales_team
staff
team_members

9. Summary

  • RENAME TABLE a TO b; or ALTER TABLE a RENAME TO b; renames instantly.
  • RENAME TABLE can rename many tables atomically, which is ideal for swaps.
  • Foreign keys and triggers follow the table; views, routines and table-level grants do not.
  • RENAME TABLE db1.t TO db2.t moves a table between databases on the same server.