How do I rename a table in MySQL?

Where, table_name is the name of an existing table and new_name is the name to which you need to change.

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE Demo(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into Demo values(1, 'Shikhar', 'Dhawan');
mysql> insert into Demo values(2, 'Jonathan', 'Trott');
mysql> insert into Demo values(3, 'Kumara', 'Sangakkara');

Now, if verify the contents of the Demo table, you can observe the created records as −

mysql> select * from Demo;
+------+------------+------------+
| ID   | First_Name | Last_Name  |
+------+------------+------------+
| 1    | Shikhar    | Dhawan     |
| 2    | Jonathan   | Trott      |
| 3    | Kumara     | Sangakkara |
+------+------------+------------+
3 rows in set (0.00 sec)

Following query renames the above created table −

mysql> RENAME TABLE Demo to Players;
Query OK, 0 rows affected (0.74 sec)

Since you have renamed the table, if you verify the contents of the table using the SELECT statement you will get an error as shown below −

mysql> select * from Demo;
ERROR 1146 (42S02): Table 'sample.demo' doesn't exist

Renaming multiple tables

Using the RENAME TABLE statement you can rename multiple tables at once following is the syntax to do so −

MySQL provides a dynamic environment that enables you to alter database items with a few basic commands. By learning how to use various statements, you can manage your databases with ease.

This tutorial contains all the commands needed to rename a column in a MySQL database.

Prerequisites

Rename MySQL Column with ALTER TABLE Command

ALTER TABLE is an essential command used to change the structure of a MySQL table. You can use it to add or delete columns, change the type of data within the columns, and even rename entire databases. The function that concerns us the most is how to utilize ALTER TABLE to rename a column.

Statements give us additional control over the renaming process. The RENAME COLUMN and

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 statements both allow for the names of existing columns to be altered. The difference is that the

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 clause can also be used to alter the data types of a column.

Rename MySQL Column with the RENAME Statement

The simplest way to rename a column is to use the ALTER TABLE command with the RENAME COLUMN clause. This clause is available since MySQL version 8.0.

Let’s illustrate its simple syntax. To change a column name, enter the following statement in your MySQL shell:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Replace

ALTER TABLE employees RENAME COLUMN id TO employ_id;

4,

ALTER TABLE employees RENAME COLUMN id TO employ_id;

5, and

ALTER TABLE employees RENAME COLUMN id TO employ_id;

6 with your table and column names. Keep in mind that you cannot rename a column to a name that already exists in the table.

For instance, to change the column id into employee_id in the table employees, you would run:

ALTER TABLE employees RENAME COLUMN id TO employ_id;

The RENAME COLUMN statement can only be used to rename a column. If you need additional functions, such as changing the data definition, or position of a column, use the

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 clause instead.

Note: The word

ALTER TABLE employees RENAME COLUMN id TO employ_id;

9 is obligatory for the

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

0 command.

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

1 is the existing syntax to rename the entire table.

Rename MySQL Column with the CHANGE Statement

The

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 clause offers important additions to the renaming process. It can be used to rename a column and change the data type of that column with the same command.

Enter the following command in your MySQL client shell to change the name of the column and its definition:

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

You can change the data type of the column or keep the existing one. In both cases you have to specify the data type as the element is mandatory.

For example, to change the column id into employee_id which has the data type VARCHAR(25) in the table employees, you would run:

ALTER TABLE employees CHANGE id employ_id VARCHAR(25);

Note: If you don't know the data type of the column you are renaming, check the structure of the table and the column definition using the

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

3 statement:

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

4.

Additional Options

You can use additional options to further manipulate table columns. The

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 also allows you to place the column in a different position in the table by using the optional

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

6 clause. For example:

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type AFTER column_x;

With the command above you can changed the name of the column, changed the data type to

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

7, and positioned the column after

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

8.

Rename Multiple MySQL Columns

MySQL allows you to rename multiple columns with a single command. This option is possible with the

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

9 and the

ALTER TABLE employees RENAME COLUMN id TO employ_id;

0 statement.

To change the names of multiple columns using the RENAME COLUMN clause, use the syntax:

ALTER TABLE table_name 
RENAME COLUMN old_column_name1 TO new_col_name1,
RENAME COLUMN old_column_name2 TO new_col_name2,
RENAME COLUMN old_column_name3 TO new_col_name3;

To change the names of multiple columns using the CHANGE clause, use the syntax:

ALTER TABLE table_name 
CHANGE old_column_name1 new_col_name1 Data Type,
CHANGE old_column_name2 new_col_name2 Data Type,
CHANGE old_column_name3 new_col_name3 Data Type;

Conclusion

You have successfully renamed an existing column in your MySQL database. This article has offered two options and provided the necessary commands. Understanding the essential ALTER TABLE statement is a precondition for exploring more complex expressions.

What is Rename command in MySQL?

Can we rename an existing table?