How to Rename Column Name in SQL?
FREE
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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:
Examples of Renaming Column Names in SQL
Let us now look at a few examples:
- Using ALTER along with the RENAME command.
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.
- Rename a column using ALTER with the CHANGEcommand.
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.
- Example to show rename column name in SQL Server
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.
Learn more
To learn the fundamentals of SQL to write queries and work progressively using multiple databases in-depth, refer to this article on SQL.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreConclusion
- 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.