Rename Column Name in SQLThe use of column names is to identify specific columns in a table. It is essential that the names of the columns must be relevant to the data present in the column for better clarity and understanding. Sometimes, we see a requirement to change the name of the column in the database to maintain the relevance of the data contained in it. SQL allows changing column names using the following command: - ALTER TABLE command that can change data types, column names, and other properties of a table.
- The RENAME COLUMN command is used along with the ALTER TABLE command to rename a column in the database.
Benefits of renaming a column in SQL:- Clear names that relate to the data in the column help improve clarity and becomes easy to differentiate between different columns and avoid confusion.
- More descriptive column names help improve readability and make the data in the table easier to understand.
- It helps to organize the data in the database efficiently.
- It simplifies column names for easier understanding.
- It helps avoid errors that can arise from using a similar name for columns in a table.
The syntax for renaming a column name in SQL will depend on the database you are using, but the basic syntax for renaming a column name in SQL is as follows: We will look at the examples to understand how to change the column names in databases such as MySQL, PostgreSQL, Microsoft SQL Server, MariaDB, Oracle, etc. Examples of renaming a column name in MySQL, PostgreSQL, MariaDB, and Oracle:Example 1: Let us consider a table named student_table, which contains the fields of stu_number, stu_name, stu_age, stu_address, stu_stream, and stu_marks. stu_number | stu_name | stu_age | stu_address | stu_stream | stu_marks |
---|
1 | Inaya | 20 | Faridabad | Science | 95 | 2 | Jai | 22 | Noida | Commerce | 80 | 3 | Ishaan | 20 | Noida | Arts | 74 | 4 | Milan | 18 | Roorkee | Science | 72 | 5 | Pari | 21 | Gurugram | Science | 81 | 6 | Khushi | 20 | Goa | Commerce | 82 | 7 | Himanshu | 23 | Meerut | Commerce | 75 | 8 | Rohit | 22 | Meerut | Commerce | 86 | 9 | Sadaf | 20 | Goa | Arts | 74 | 10 | Asha | 19 | Gurugram | Arts | 70 |
We will rename the column name from stu_number to stu_id. The query given below will change the column name: Query: Output: It is evident from the output that the column name has been changed from stu_number to stu_id. stu_id | stu_name | stu_age | stu_address | stu_stream | stu_marks |
---|
1 | Inaya | 20 | Faridabad | Science | 95 | 2 | Jai | 22 | Noida | Commerce | 80 | 3 | Ishaan | 20 | Noida | Arts | 74 | 4 | Milan | 18 | Roorkee | Science | 72 | 5 | Pari | 21 | Gurugram | Science | 81 | 6 | Khushi | 20 | Goa | Commerce | 82 | 7 | Himanshu | 23 | Meerut | Commerce | 75 | 8 | Rohit | 22 | Meerut | Commerce | 86 | 9 | Sadaf | 20 | Goa | Arts | 74 | 10 | Asha | 19 | Gurugram | Arts | 70 |
Example 2: Let us consider another table named product_table, which contains the fields of id, name, quantity, and money. id | name | quantity | money |
---|
1 | Chocolates | 20 | 200 | 2 | Candies | 22 | 110 | 3 | Lollipops | 20 | 100 | 4 | Chips | 18 | 90 | 5 | Apples | 21 | 600 | 6 | Biscuits | 20 | 200 | 7 | Chochos | 23 | 230 | 8 | Waffle Chips | 22 | 220 | 9 | Jelly Candies | 20 | 400 | 10 | Popcorn | 19 | 380 |
We will rename the column name from money to price. The query given below will change the column name: Query: Output: It is clear from the output that the column name has been changed from money to price. id | name | quantity | price |
---|
1 | Chocolates | 20 | 200 | 2 | Candies | 22 | 110 | 3 | Lollipops | 20 | 100 | 4 | Chips | 18 | 90 | 5 | Apples | 21 | 600 | 6 | Biscuits | 20 | 200 | 7 | Chochos | 23 | 230 | 8 | Waffle Chips | 22 | 220 | 9 | Jelly Candies | 20 | 400 | 10 | Popcorn | 19 | 380 |
Example 3: Let us consider a table named student_table, which contains the fields of stu_number, stu_name, stu_age, stu_address, stu_stream, and marks. stu_number | stu_name | stu_age | stu_address | stu_stream | marks |
---|
11 | Robin | 19 | Seattle | Commerce | 85 | 12 | Jack | 20 | Washington | Arts | 95 | 13 | Michael | 21 | Austin | Science | 84 | 14 | Justin | 19 | Columbus | Commerce | 75 | 15 | Ariana | 20 | Austin | Commerce | 80 | 16 | Selena | 21 | Washington | Arts | 90 | 17 | Katrina | 22 | Columbus | Arts | 89 | 18 | Tom | 23 | Columbus | Commerce | 85 | 19 | Henry | 21 | Austin | Science | 79 | 20 | Alexander | 20 | Seattle | Science | 90 |
We will rename the column name from marks to stu_marks. The query given below will change the column name: Query: Output: It is evident from the output that the column name has been changed from marks to stu_marks. stu_number | stu_name | stu_age | stu_address | stu_stream | stu_marks |
---|
11 | Robin | 19 | Seattle | Commerce | 85 | 12 | Jack | 20 | Washington | Arts | 95 | 13 | Michael | 21 | Austin | Science | 84 | 14 | Justin | 19 | Columbus | Commerce | 75 | 15 | Ariana | 20 | Austin | Commerce | 80 | 16 | Selena | 21 | Washington | Arts | 90 | 17 | Katrina | 22 | Columbus | Arts | 89 | 18 | Tom | 23 | Columbus | Commerce | 85 | 19 | Henry | 21 | Austin | Science | 79 | 20 | Alexander | 20 | Seattle | Science | 90 |
Examples of renaming a column name in Microsoft SQL Server:Example 1: Let us consider one more table named employee_table, which contains the fields of emp_id, emp_id, emp_address, department, and emp_salary. emp_id | emp_name | emp_address | department | emp_salary |
---|
50 | John | Washington | Sales | 25000 | 51 | Shirley | Austin | Software Testing | 50000 | 52 | Daniel | Columbus | Sales | 40000 | 53 | Anthony | Austin | Content Writing | 20000 | 54 | James | Chicago | HR | 45000 | 55 | Helen | Phoenix | Software Testing | 55000 | 56 | Nicole | Chicago | Sales | 35000 | 57 | Nick | Columbus | HR | 40000 | 58 | Peter | Phoenix | Content Writing | 25000 | 59 | Ashley | Washington | Content Writing | 35000 |
We will rename the column name from department to emp_department. The query given below will change the column name: Query: Output: It is clear from the output that the column name has been changed from department to emp_department. emp_id | emp_name | emp_address | emp_department | emp_salary |
---|
50 | John | Washington | Sales | 25000 | 51 | Shirley | Austin | Software Testing | 50000 | 52 | Daniel | Columbus | Sales | 40000 | 53 | Anthony | Austin | Content Writing | 20000 | 54 | James | Chicago | HR | 45000 | 55 | Helen | Phoenix | Software Testing | 55000 | 56 | Nicole | Chicago | Sales | 35000 | 57 | Nick | Columbus | HR | 40000 | 58 | Peter | Phoenix | Content Writing | 25000 | 59 | Ashley | Washington | Content Writing | 35000 |
Example 2: Let us consider one more table named book_table, which contains the fields of id, book_name, author, pages, and price. id | book_name | author | pages | price |
---|
1 | A Place Called Home | Preeti Shenoy | 324 | 250 | 2 | Pride, Prejudice, and Punditry | Shashi Tharoor | 600 | 630 | 3 | Lal Salam | Smriti Irani | 256 | 65 | 4 | The Fault in Our Stars | John Green | 352 | 250 | 5 | The Book Thief | Markus Zusak | 624 | 295 | 6 | Animal Farm | George Orwell | 88 | 120 | 7 | The Ten Trillion Dream | Subhash Chandra Garg | 712 | 650 | 8 | Unfinished | Priyanka Chopra Jonas | 280 | 420 | 9 | The Princess Bride | William Goldman | 512 | 780 | 10 | Life of Pi | Yann Martel | 352 | 240 |
We will rename the column name from book_name to book_list. The query given below will change the column name: Query: Output: It is clear from the output that the column name has been changed from book_name to book_list. id | book_list | author | pages | price |
---|
1 | A Place Called Home | Preeti Shenoy | 324 | 250 | 2 | Pride, Prejudice, and Punditry | Shashi Tharoor | 600 | 630 | 3 | Lal Salam | Smriti Irani | 256 | 65 | 4 | The Fault in Our Stars | John Green | 352 | 250 | 5 | The Book Thief | Markus Zusak | 624 | 295 | 6 | Animal Farm | George Orwell | 88 | 120 | 7 | The Ten Trillion Dream | Subhash Chandra Garg | 712 | 650 | 8 | Unfinished | Priyanka Chopra Jonas | 280 | 420 | 9 | The Princess Bride | William Goldman | 512 | 780 | 10 | Life of Pi | Yann Martel | 352 | 240 |
Example 3: Let us consider one more table named employee_table, which contains the fields of emp_id, emp_name, emp_address, department, and salary. emp_id | emp_name | emp_address | department | salary |
---|
50 | John | Washington | Sales | 25000 | 51 | Shirley | Austin | Software Testing | 50000 | 52 | Daniel | Columbus | Sales | 40000 | 53 | Anthony | Austin | Content Writing | 20000 | 54 | James | Chicago | HR | 45000 | 55 | Helen | Phoenix | Software Testing | 55000 | 56 | Nicole | Chicago | Sales | 35000 | 57 | Nick | Columbus | HR | 40000 | 58 | Peter | Phoenix | Content Writing | 25000 | 59 | Ashley | Washington | Content Writing | 35000 |
We will rename the column name from salary to emp_salary. The query given below will change the column name: Query: Output: It is clear from the output that the column name has been changed from salary to emp_salary. emp_id | emp_name | emp_address | department | emp_salary |
---|
50 | John | Washington | Sales | 25000 | 51 | Shirley | Austin | Software Testing | 50000 | 52 | Daniel | Columbus | Sales | 40000 | 53 | Anthony | Austin | Content Writing | 20000 | 54 | James | Chicago | HR | 45000 | 55 | Helen | Phoenix | Software Testing | 55000 | 56 | Nicole | Chicago | Sales | 35000 | 57 | Nick | Columbus | HR | 40000 | 58 | Peter | Phoenix | Content Writing | 25000 | 59 | Ashley | Washington | Content Writing | 35000 |
Conclusion:In this article, you have learned how to rename column names in SQL. You have understood that there is a slightly different way of renaming column names in different SQL databases. You have learned different ways to rename column names in SQL databases such as MySQL, Oracle, MariaDB, PostgreSQL, and Microsoft SQL Server.
|