JIYIK CN >

This article will explain how to rename a table in a MySQL database.

Often, an organization's product requirements change, and as a result, the names of tables and columns in a particular database need to be constantly changed. These changes will help reflect the updated information. MySQL uses several techniques to help us do this efficiently.

To change the name of a specific table, we use RENAME TABLEthe statement as shown below.

RENAME TABLE old_table_name TO new_table_name;

We can rename tables in MySQL using the following two techniques.

• Usage RENAME TABLEmethod.
• Usage ALTER TABLEmethod.

Before we get started, let's create a dummy dataset to work with. Here, we create a table student_detailsand a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Now let's student_detailsrename the table to student_informationtable.

RENAMERename a table in MySQL using

We can renamedo this using the statement. This statement can be written as RENAME previous_table_name to new_name_to_be_assigned;in MySQL. To student_detailsperform the above operation on the table, we can use the following query.

RENAME TABLE student_details TO student_information;

Now, let's check student_informationthe table using the following query.

SELECT * from student_information;

The above mentioned query will give us the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

It shows that the table has been renamed successfully and there is no tussle in the table data.

ALTER TABLERename a table in MySQL using

We can also use the RENAME statement in MySQL ALTER TABLEto rename the table. The syntax for this operation is as follows.

ALTER TABLE previous_table_name RENAME new_name_to_be_assigned;

ALTER TABLEIn order to rename the table using technique student_details, we can use the following query.

ALTER TABLE student_details RENAME student_information;

Now, let's check student_informationthe table using the following query.

SELECT * from student_information;

The above mentioned query will give us the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller