← SQL roadmap

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

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL checks staff doesn't exist.
  2. 2.The table's name in the data dictionary changes from employees to staff.
  3. 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

Intermediate 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

FormMultiple tables at oncePortable
RENAME TABLE a TO bYes (MySQL)MySQL only
ALTER TABLE a RENAME TO bNoMySQL, PostgreSQL, SQLite, Snowflake

14Practical exercises

Beginner1. Rename departments to depts.

Reveal solution (15)
Solution · run it

Data is unchanged.

Beginner2. Rename employees to emp and count rows.

Reveal solution (15)
Solution · run it

Still 5 rows.

Beginner3. List all table names after renaming employees to staff.

Reveal solution (15)
Solution · run it

MySQL equivalent: SHOW TABLES;

Intermediate4. Rename both tables: employees → staff, departments → teams.

Reveal solution (15)
Solution · run it

MySQL: RENAME TABLE employees TO staff, departments TO teams;

Intermediate5. Rename employees to staff and join it with departments.

Reveal solution (15)
Solution · run it

Joins work with the new name.

Intermediate6. Show the error when querying the old name.

Reveal solution (15)
Solution · run it

Expected: no such table.

Advanced7. Swap employees with a filtered employees_new (salary ≥ 60000).

Reveal solution (15)
Solution · run it

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)
Solution · run it

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “RENAME TABLE / ALTER TABLE RENAME TO” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.