How to Rename Column Name in SQL?

Methods to Rename a Column in SQL

We can use the following methods to rename a column in SQL.

We can change the name of a column by using the ALTER command along with the RENAME COLUMN command.

Syntax:

The above syntax is applicable to MySQL, Oracle, and Postgres SQL.

Another way to rename a column name is by using the CHANGE command along with the ALTER command.

Syntax:

The above command is applicable to MySQL and MariaDB.

ALTER command cannot be used to change the column name while using SQL Server. For SQL Server sp_rename command is used.

Syntax:

Let us now look at a few examples:

Let us consider the following Table Employees_detail:

Emp_ID	Emp_name	Emp_gender	Emp_age
101	Amar Rathore	Male	27
102	Dimple Khanna	Female	28
103	Mayuri Chatterji	Female	27
104	Karthik Padman	Male	30
105	Aisha Khan	Female	29
106	Dianna	Female	27
107	Jaspreet	Male	30

We have to rename the column name Emp_age to age_of_emp.

SQL Query:

Output:

Emp_ID	Emp_name	Emp_gender	age_of_Emp
101	Amar Rathore	Male	27
102	Dimple Khanna	Female	28
103	Mayuri Chatterji	Female	27
104	Karthik Padman	Male	30
105	Aisha Khan	Female	29
106	Dianna	Female	27
107	Jaspreet	Male	30

Hence, we see that the column name Emp_age is now changed to age_of_Emp.

Let us consider a table faculty.

f_ID	f_name	f_yoe	f_age
201	Dr. Ajeya Krishna	10	47
202	Dr. Sudhanshu Jha	15	48
203	Dr. Gargi Sen	13	47
204	Dr. Karthik Rao	16	50
205	Dr. Aiman Shadab	8	49
206	Dr. M Dsouza	10	57
207	Dr.Rajshekhar	12	50

We will rename column name f_name to phdf_name.

SQL query

Output:

f_ID	phdf_name	f_yoe	f_age
201	Dr. Ajeya Krishna	10	47
202	Dr. Sudhanshu Jha	15	48
203	Dr. Gargi Sen	13	47
204	Dr. Karthik Rao	16	50
205	Dr. Aiman Shadab	8	49
206	Dr. M Dsouza	10	57
207	Dr.Rajshekhar	12	50

The column name is changed from f_name to phdf_name.

Let us consider a table product_details:

Prod_ID	brand	chocolate	sales/day
055	Nestle	Milky Bar	10000000
056	Amul	Dark Chocolate	500000
057	Gandour	Safari	200000
058	Cadbury	Dairy Milk	1000000
059	Nestle	Kitkat	500000
060	Cadbury	Silk	300000
061	Parle	Melody	5000000

We will now rename the column name chocolate to prod_name.

SQL Query:

Output:

Prod_ID	brand	prod_name	sales/day
055	Nestle	Milky Bar	10000000
056	Amul	Dark Chocolate	500000
057	Gandour	Safari	200000
058	Cadbury	Dairy Milk	1000000
059	Nestle	Kitkat	500000
060	Cadbury	Silk	300000
061	Parle	Melody	5000000

Hence, the column name chocolate is now changed to prod_name.

To learn the fundamentals of SQL to write queries and work progressively using multiple databases in-depth, refer to this article on SQL.

We learned the different ways to rename column names in SQL.
The most common ways that are supported by MySQL, Oracle, PostgreSQL, and MariaDB are to use the ALTER command using the RENAME command and the ALTER command using the CHANGE COLUMN command.
To change a column name in SQL Server sp_rename command is used, and here ALTER command cannot be used.

It's now your turn to play around with SQL queries and try out to rename column names in SQL.