SQL Rename Table

If you would like to change the table name to a more relevant name.

This change is possible in two ways :

Use ALTER TABLE statement
Use RENAME TABLE statement

Syntax

The syntax for ALTER TABLE.


ALTER TABLE Old_Table_Name RENAME New_Table_Name;

The syntax for RENAME TABLE.


RENAME TABLE Old_Table_Name TO New_Table_Name;

You can also change the column name by using ALTER TABLE statement.


ALTER TABLE Table_name
RENAME COLUMN Old_column_name to New_column_Name;

RENAME Statement gives more flexibility. That means you can change the name of multiple tables by using a single RENAME TABLE statement.

Examples:How to RENAME database Table?

Example to use ALTER TABLE

Consider the following demo 'student' table.


+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Aruna   |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+

Following is the example query statement to use ALTER TABLE statement for rename database table from 'Student' into 'Student_Personal'.


mysql> ALTER TABLE Student RENAME Student_Personal;
Query OK, 0 rows affected (0.03 sec)

Now, you have to see the output by selecting all records from the 'Student-Personal' table.


mysql> SELECT * FROM Student_Personal;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Aruna   |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+
7 rows in set (0.00 sec)

Example to use RENAME TABLE

Consider the following demo 'Student_Details' table.


+--------+-----------+---------+-------------+-------------------+----------+-----------+
| RollNo | Name      | PhoneNo | Laptop_Info | Postal_address    | Pin_code | City      |
+--------+-----------+---------+-------------+-------------------+----------+-----------+
|      1 | Kamalesh  |  689900 | yes         | 20 bharath nagar  |   600002 | Chennai   |
|      2 | Aadhi     |  683200 | yes         | 2 bhrindha street |   600001 | Pandy     |
|      3 | Sunil     |  631200 | NULL        | 2 panny street    |    60020 | Mumbai    |
|      4 | Makesh    |  634560 | yes         | 2nd street        |   610020 | Chennai   |
|      5 | Bavithra  |  634110 | yes         | 7 Kannan Street   |   600010 | Pandy     |
|      6 | Boomi     |  234110 | NULL        | 23 lala Street    |   602310 | Bangalore |
|      7 | Yashvanth |  204110 | NULL        | 20 Muthu Street   |   602312 | Bangalore |
|      8 | Dev       |  204890 | yes         | 3rd Street        |   602342 | Mumbai    |
+--------+-----------+---------+-------------+-------------------+----------+-----------+

Below is the example query statement to use RENAME TABLE statement for rename database table from 'Student_Details' into 'Student_Address'.


mysql> RENAME TABLE Student_Details To Student_Address;
Query OK, 0 rows affected (0.03 sec)

After renaming the table, you see the output by selecting all records from the 'Student_Address' table.


mysql> SELECT * FROM Student_Address;
+--------+-----------+---------+-------------+-------------------+----------+-----------+
| RollNo | Name      | PhoneNo | Laptop_Info | Postal_address    | Pin_code | City      |
+--------+-----------+---------+-------------+-------------------+----------+-----------+
|      1 | Kamalesh  |  689900 | yes         | 20 bharath nagar  |   600002 | Chennai   |
|      2 | Aadhi     |  683200 | yes         | 2 bhrindha street |   600001 | Pandy     |
|      3 | Sunil     |  631200 | NULL        | 2 panny street    |    60020 | Mumbai    |
|      4 | Makesh    |  634560 | yes         | 2nd street        |   610020 | Chennai   |
|      5 | Bavithra  |  634110 | yes         | 7 Kannan Street   |   600010 | Pandy     |
|      6 | Boomi     |  234110 | NULL        | 23 lala Street    |   602310 | Bangalore |
|      7 | Yashvanth |  204110 | NULL        | 20 Muthu Street   |   602312 | Bangalore |
|      8 | Dev       |  204890 | yes         | 3rd Street        |   602342 | Mumbai    |
+--------+-----------+---------+-------------+-------------------+----------+-----------+
8 rows in set (0.00 sec)

Example to change the column name

Consider the following demo 'Student_mark' table.


+--------+---------+-----------+------+
| RollNo | Name    | City      | Mark |
+--------+---------+-----------+------+
|    101 | Sakthi  | Chennai   |   80 |
|    102 | Bala    | Chennai   |   86 |
|    103 | Chandra | Bangalore |   89 |
|    104 | Madhan  | Goa       |   80 |
|    105 | Jose    | Kerala    |   82 |
|    106 | Jithu   | Kerala    |   85 |
|    107 | Veni    | Bangalore |   85 |
|    108 | Aruna   | Delhi     |   75 |
+--------+---------+-----------+------+

Below is the example query statement to change the column name 'RollNo' into 'ID_NO' in the 'Student_mark' table<./p>


mysql> ALTER TABLE Student_mark
RENAME COLUMN RollNo TO ID_No;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

After changing the column's name, you see the output by selecting all records from the 'Student_mark' table.


mysql> SELECT * FROM student_mark;
+-------+---------+-----------+------+
| ID_No | Name    | City      | Mark |
+-------+---------+-----------+------+
|   101 | Sakthi  | Chennai   |   80 |
|   102 | Bala    | Chennai   |   86 |
|   103 | Chandra | Bangalore |   89 |
|   104 | Madhan  | Goa       |   80 |
|   105 | Jose    | Kerala    |   82 |
|   106 | Jithu   | Kerala    |   85 |
|   107 | Veni    | Bangalore |   85 |
|   108 | Aruna   | Delhi     |   75 |
+-------+---------+-----------+------+
8 rows in set (0.00 sec)

Summary

What is the command used to rename the table?
There are two commands used to rename the database table. They are ALTER TABLE command and RENAME TABLE command.
How to change the name of a column in the table?
The SQL ALTER TABLE command uses to change the name of the column in the table.
The syntax is:
```
ALTER TABLE Table_name
RENAME COLUMN Old_column_name to New_column_Name;
```
How do you rename the database table in MySQL?
you rename the database table by using RENAME TABLE command and ALTER TABLE Command,

What is the syntax for RENAME TABLE?


RENAME TABLE Old_Table_Name TO New_Table_Name;

What is the syntax for ALTER TABLE to change the existing table name?
```
ALTER TABLE Old_Table_Name RENAME TO New_Table_Name;
```

<<Previous - SQL ALTER TABLE RENAME

Next - SQL INSERT INTO >>

SQL RENAME TABLE

Syntax

Examples:How to RENAME database Table?

Summary