MySQL Rename Column

Introduction to MySQL Rename Column

To RENAME an existing column we use the “CHANGE” command along with the “ALTER” command. We can change the table names with the command “RENAME”. MySQL Rename command is used to rename the existing table or an existing column. We can use “Alter” to rename the table, but to rename more than one table can’t be done by “Alter”. We can do this by using the “RENAME” table. Few privileges are essential to the “RENAME” table. ALTER and DROP privilege is essential for the original table, and CREATE and INSERT privilege is essential to the New table.

RENAME can be used to move tables between different databases. To be precise, we can use the RENAME table to move a table from one database to another. RENAME works for a view of the same database but we can’t perform the rename with the different databases.

Syntax:

To RENAME column from a table

ALTER TABLE<TABLE_NAME>CHANGE<OLD_COLUMNNAME>
<NEW_COLUMNNAME><DATA_TYPE>

To RENAME column from a table:

ALTER TABLE<TABLE_NAME> CHANGE <OLD_COLUMNNAME1><NEW_COLUMNNAME1><DATA_TYPE>,
CHANGE <OLD_COLUMNNAME2><NEW_COLUMNNAME2><DATA_TYPE>,
.
.
.
CHANGE <OLD_COLUMNNAME_N><NEW_COLUMNNAME_N><DATA_TYPE>

To RENAME a single table, we use the below syntax:

RENAME TABLE<old_tablename>TO<New_tablename>

To RENAME multiple tables at a time:

RENAME TABLE<old_tablename1>TO<New_tablename1>,
<old_tablename2>TO<New_tablename2>,
<old_tablename3>TO<New_tablename3>;

Using RENAME between different databases:

RENAME TABLE<Current_database>.<tablename>TO<other_database><tablename>

How to RENAME Column in MYSQL?

Create a test table :

create table test
(
id int
);

Insert data into the test table:

INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (3);
select * from test;

Output:

Let us describe the table and see the output:

DESC test;

Output:

Rename the column from“ID” of INT datatype to “ID” of varchar(20).

ALTER TABLE TEST CHANGE ID ID VARCHAR(10);

Let us describe the table and see the output:

DESC TEST;

Output:

Multiple table RENAME:

create table Data
(
id int,
name varchar(20),
location varchar(20)
);

Insert data into the table:

INSERT INTO DATA VALUES (1,'Rose','United Kingdom');
INSERT INTO DATA VALUES (2,'Jack','United states');
INSERT INTO DATA VALUES (3,'Will','Britain');
select * from DATA;

Output:

Let us describe the table and see the output: –

DESC DATA;

Output:

Now let us change the column “name” of datatype varchar (20) to “name” of datatype varchar(30). Let us increase the size.

ALTER TABLE DATA CHANGE NAME NAME VARCHAR(30),
CHANGE LOCATION LOCATION VARCHAR(30);

Let us describe the table and see the output: –

DESC DATA;

Output:

Example of MySQL Rename Column

Let us create the table and insert data into it and perform rename operation.

create table Collegedata
(
college_id INT,
college_name VARCHAR(30),
No_of_students INT,
Location VARCHAR(20)
);

Insert data into the table:

insert into collegedata values(1890,'Narayana pvt college', 700000,'Hyderabad');
insert into collegedata values(2890,'St.Josephpvt college', 560000,'Kerala');
insert into collegedata values(3890,'Private Plan pvt college', 230000,'Hyderabad');
insert into collegedata values(4890,'Chorniclepvt college', 60000,'Maharastra');
insert into collegedata values(5890,'Number one pvt college', 780000,'Hyderabad');
insert into collegedata values(6890,'Startuppvt college', 500000,'Uttar Pradesh');

Select the data from the table:

select * from collegedata;

Output:

Let us describe the table and see the output: –

DESC collegedata;

Output:

Now let us change the column “college_name” of datatype varchar (30) to “college_name” of datatype varchar (35). Let us increase our size. The column “location” of datatype varchar (20) to “location” of datatype varchar(30).

ALTER TABLE collegedata CHANGE college_name college_name VARCHAR(35),
CHANGE LOCATION LOCATION VARCHAR(30);

Let us describe the table and see the output:

DESC collegedata;

Output:

Recommended Articles

This is a guide to the MySQL rename column. Here we discuss the introduction to MySQL rename column along with query example for understanding better. You may also have a look at the following articles to learn more –

1. MySQL Administration
2. MySQL Queries
3. Table in MySQL
4. MySQL Database Repair