Topic 6 of 52 · Beginner
RENAME TABLE / ALTER TABLE RENAME TO
Give an existing table a new name, keeping its data.
01RENAME TABLE / ALTER TABLE RENAME TO
Module 02 – Database & Table Management (DDL)
02What is it?
Renaming a table changes only its name. Rows, columns, indexes and constraints stay. MySQL has two forms: RENAME TABLE old TO new, and ALTER TABLE old RENAME TO new.
03Why do we need it?
Naming conventions (tbl_emp → employees), or swapping a freshly-loaded table into place: load into employees_new, then rename it to employees in one atomic step.
04Syntax
RENAME TABLE old_name TO new_name [, old2 TO new2];
-- or
ALTER TABLE old_name RENAME TO new_name;05Parameter explanation
- old_name
- Existing table.
- new_name
- New name; must not already exist.
- multiple pairs
- RENAME TABLE can rename several tables atomically in one statement (MySQL).
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL checks staff doesn't exist.
- 2.The table's name in the data dictionary changes from employees to staff.
- 3.Queries must use staff; employees no longer exists.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
Nightly rebuilds load data into sales_staging, validate it, then swap names (RENAME TABLE sales TO sales_old, sales_staging TO sales) so dashboards never see a half-loaded table.
12Common mistakes
RENAME employees TO staff;
Missing TABLE keyword.
RENAME TABLE employees TO staff;
ALTER TABLE employees RENAME TO departments;
departments already exists.
Pick an unused name.
SELECT * FROM employees; -- after renaming
Old name no longer exists.
SELECT * FROM staff;
13Important comparisons
| Form | Multiple tables at once | Portable |
|---|---|---|
| RENAME TABLE a TO b | Yes (MySQL) | MySQL only |
| ALTER TABLE a RENAME TO b | No | MySQL, PostgreSQL, SQLite, Snowflake |
14Practical exercises
Beginner1. Rename departments to depts.
Reveal solution (15)
Data is unchanged.
Beginner2. Rename employees to emp and count rows.
Reveal solution (15)
Still 5 rows.
Beginner3. List all table names after renaming employees to staff.
Reveal solution (15)
MySQL equivalent: SHOW TABLES;
Intermediate4. Rename both tables: employees → staff, departments → teams.
Reveal solution (15)
MySQL: RENAME TABLE employees TO staff, departments TO teams;
Intermediate5. Rename employees to staff and join it with departments.
Reveal solution (15)
Joins work with the new name.
Intermediate6. Show the error when querying the old name.
Reveal solution (15)
Expected: no such table.
Advanced7. Swap employees with a filtered employees_new (salary ≥ 60000).
Reveal solution (15)
3 rows remain in the live table; the old one is kept as backup.
Advanced8. After a swap, roll back to the old version.
Reveal solution (15)
Keeping the old table makes rollback a rename.
16Key takeaways
- Renaming keeps data, indexes and constraints.
- RENAME TABLE can swap several tables atomically in MySQL.
- Update views and code using the old name.
17Knowledge check
1. Which form renames several tables atomically in MySQL?
2. Does renaming a table delete rows?
3. Why rename in a blue/green load?
Interview questions
How do you replace a table with zero downtime in MySQL?
Load a new table, then RENAME TABLE live TO live_old, live_new TO live; — one atomic statement.
Lesson test
Take an 8-question test on “RENAME TABLE / ALTER TABLE RENAME TO” — concepts, output prediction, bug spotting and interview questions.