Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-rename-table-statement/ below:

MySQL RENAME TABLE Statement - GeeksforGeeks

Last Updated : 23 Jul, 2025

The MySQL RENAME TABLE statement is a simple yet powerful command that allows you to change the name of an existing table in your database. This can be useful for various reasons, such as updating table names to better reflect their content or restructuring your database without losing any data.

By using RENAME TABLE, you can easily and quickly rename one or more tables in a single command, ensuring that your database remains organized and up-to-date.

The RENAME TABLE statement in MySQL allows you to change the names of tables within a database, helping maintain organization and adaptability in data management.

Syntax:

RENAME TABLE old_table_name TO new_table_name [, ...];

-- Create a table and insert data
CREATE TABLE old_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO old_table (id, name) VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');
-- Rename the table 'old_table' to 'new_table'

RENAME TABLE old_table TO new_table;
-- Check if the table is renamed

SELECT * FROM new_table;

Output:

Explanation: After renaming 'old_table' to 'new_table', the SELECT statement verifies the renaming. It retrieves all rows and columns from 'new_table', confirming that the table now reflects the new name and retains the inserted data of 'John', 'Alice', and 'Bob'.

To rename multiple tables MySQL RENAME TABLE statement can be used.

-- Create two tables
CREATE TABLE old_table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE old_table2 (
    id INT PRIMARY KEY,
    description TEXT
);
-- Insert data into both tables

INSERT INTO old_table1 (id, name) VALUES (1, 'John'), (2, 'Alice');
INSERT INTO old_table2 (id, description) VALUES (1, 'Description 1'), (2, 'Description 2');
-- Rename both tables simultaneously

RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2;
-- Check if tables are renamed

SELECT * FROM new_table1;
SELECT * FROM new_table2;

Output:

Output:

Explanation: After renaming 'old_table1' to 'new_table1' and 'old_table2' to 'new_table2', the SELECT statements verify the renaming:

• new_table1: Displays rows with IDs 1 and 2, showing names 'John' and 'Alice'.
• new_table2: Displays rows with IDs 1 and 2, showing descriptions 'Description 1' and 'Description 2'.

In MySQL, we can also use the ALTER TABLE statement to rename the existing table in the current database. The following example will help you to understand it better:

-- Create a table and insert data
CREATE TABLE old_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO old_table (id, name) VALUES (1, 'John'), (2, 'Alice');
-- Rename the table using ALTER TABLE

ALTER TABLE old_table RENAME TO new_table;
-- Check if the table is renamed

SELECT * FROM new_table;

Output:

Explanation: After renaming 'old_table' to 'new_table' using ALTER TABLE, the SELECT statement verifies the renaming. It retrieves all rows and columns from 'new_table', confirming that the table now reflects the new name 'new_table' and retains the inserted data of 'John' and 'Alice'.

In MySQL, temporary tables are tables that allow us to keep temporary data, which is visible and accessible in the current session.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
-- Insert data into the temporary table

INSERT INTO temp_table(id,name) VALUES (1,'John'),(2,'Alice');
-- Rename the temporary table

ALTER TABLE temp_table RENAME TO new_temp_table;
-- Check if the temporary table is renamed

SELECT * FROM new_temp_table;

Output:

Explanation: After renaming the temporary table 'temp_table' to 'new_temp_table' using ALTER TABLE, the SELECT statement confirms the renaming. It retrieves all rows and columns from 'new_temp_table', verifying that the temporary table has been successfully renamed while retaining the inserted data of 'John' and 'Alice'.

In Conclusion , we discussed how to rename a table, and also how to rename multiple tables. And how to rename a temporary table. RENAME TABLE and ALTER statements are used to rename tables. It's a quick and efficient way to update table names without messing with the data or structure. You can easily rename one or more tables in one go, making it a handy tool for managing your database. It ensures a smooth transition without any disruption to the data.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4