It may often happen that, we see a requirement to change the name of the column in the database to serve their purpose. With the help of SQL or Structured Query Language, the Database Administrators store, update, manipulate and retrieve data in relational databases. So, in this article let us understand how to rename a column name in SQL.
So let us get started then,
What is SQL?
SQL or most commonly known as SEQUEL is a Structured Query Language used for managing and accessing the databases. It is based on the English language and is designed in a way to easily retrieve, manipulate and access the data. If you wish to know more about the basics of SQL in-depth, you can refer to the article on SQL Basics. In SQL, there are various statements/ commands used to manipulate data. One such manipulation, which is quite popular in databases is renaming the column name in SQL.
So, let us understand how to use the RENAME command in SQL.
What is RENAME command in SQL?
This command is used to change the name of a column to a new column name. It is also used to change the table to a new table name. Let us understand how to use this command in different databases. But, before that, let us consider the following table to understand all the examples:
| BID | BName | Genre | Price |
1 | Trick Mirror | Psychology | 200 |
2 | Daisy Jones | Mystery | 350 |
3 | Lady in the lake | Mystery | 250 |
4 | Miracle Creek | Thriller | 450 |
5 | Disappearing Earth | Drama | 300 |
How to Rename column name in SQL?
SQL ALTER TABLE Statment
The existing table can be deleted, dropped, renamed or added, by using the ALTER TABLE command.
The constraints that we add in the table can also be dropped by using the ALTER TABLE command.
ADD COLUMN – ALTER TABLE
We use ADD command to add any column in the existing table.
syntax
ALTER TABLE table_name ADD column_name datatype;
Example
ALTER TABLE School ADD Address varchar (255) ;
The above SQL command adds an address column to the school table.
DROP TABLE – ALTER TABLE
We use DROP command to delete the table from the data base (there are some databases that don’t allow to drop the table).
Syntax
ALTER TABLE table_name DROP COLUMN column_name ;
Example
ALTER TABLE School DROP COLUMN Address;
The above SQL command deletes the address column from the School table.
RENAME TABLE – ALTER TABLE
To rename any column in the existing table we mention Old name and the new name using RENAME command.
Syntax
ALTER TABLE table_name RENAME COLUMN old_name to new_name ;
Example
ALTER TABLE School
RENAME COLUMN phone to contact ;
The above SQL command renames the existing column Phone to Contact in the School table.
ALTER/MODIFY DATATYPE – ALTER TABLE
To ALTER/MODIFY data type of any existing column, we use
ALTER /MODIFY command, specifically depends on the platform we are using for eg:
MySQL / Oracle ( pre 10G version )
In platforms like MySQL / Oracle ( pre 10G version) MODIFY ALTER command is used;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Oracle 10G and later
In platforms like Oracle (10G and later ), the MODIFY command is used;
ALTRE TABLE table_name MODIFY column_name datatype;
SQL Server /MS Access
In platforms like SQL Server / MS Access ALTER COLUMN command is used:
ALTER TABLE table_name ALTER COLUMN column_name datatype;
SQL ALTER TABLE – Example
Adding column in existing table
Take a look at this Student table ;
| Id | First name | Last Name | City |
1 | Rohan | Yadav | Jalandhar |
2 | Sam | Sharma | Delhi |
3 | Sivam | Devops | Mumbai |
Now let us add one column to this existing table:
Syntax
ALTER TABLE Student ADD dateOfBirth int;
Output;
| Id | First Name | Last Name | City | Date of Birth |
1 | Rohan | Yadav | Jalandhar | |
2 | Sam | Sharma | Delhi | |
3 | Sivam | Devops | Mumbai |
In the above table a new column has been added i.e dateOfBirth.
SQL CHANGE DATATYPE – Example
In the table below dateOfBirth column has the datatype of varchar(), that is why months name can be written in alphabet too.
Take a look at this table :
| Id | First Name | Last Name | City | Date of Birth |
1 | Rohan | Yadav | Jalandhar | 12/12/1985 |
2 | Sam | Sharma | Delhi | 2/7/1987 |
3 | Sivam | Devops | Mumbai | 2/5/1986 |
We can you this formate to change datatype.
ALTER TABLE Student; ALTER COLUMN dateOfBirth DATE ;
Output;
Now the column dateOfBirth will have DATE type and you can store data in form of year on two / four format.
SQL DROP COLUMN – Example
Take a look at the following table ;
This table contains a column named last_name , let us drop the last_name column;
Syntax
ALTER TABLE Student; DROP COLUMN last_name;
Output:
In the above table we can notice that last_name column from the Student table has been dropped.
How to Change Column Name in mysql, MariaDB, Oracle and PostgreSQL
To rename a column name in MySQL, MariaDB, Oracle, and PostgreSQL, you can follow the below syntax:
Syntax
ALTER TABLE TableName; RENAME COLUMN OldColumnName TO NewColumnName;
Example:
Write a query to rename the column name “BID” to “BooksID”.
ALTER TABLE Books; RENAME COLUMN BID TO BooksID;
For a detailed, You can even check out the details of relational databases, functions, and queries, variables, etc with the SQL Training.
On executing the above query, you will see the following output:
Output:
| BooksID | BName | Genre | Price |
1 | Trick Mirror | Psychology | 200 |
2 | Daisy Jones | Mystery | 350 |
3 | Lady in the lake | Mystery | 250 |
4 | Miracle Creek | Thriller | 450 |
5 | Disappearing Earth | Drama | 300 |
You can also use the CHANGE keyword to rename a column name as follows:
Syntax
ALTER TABLE TableName CHANGE COLUMN OldColumnName NewColumnName Data Type;
Example:
Write a query to rename the column name “BID” to “BooksID”.
ALTER TABLE Books; CHANGE COLUMN BID BooksID INT;
On executing this query, you will see the output the same as the above output.
How to Rename Column Name in SQL Server
The process of renaming column name is MS SQL Server is different when compared to the other databases. In MS SQL Server, you have to use the stored procedure called sp_rename.
Syntax
sp_rename 'TableName.OldColumnName', 'New ColumnName', 'COLUMN';
Example:
Write a query to rename the column name “BID” to “BooksID”.
sp_rename 'Books.BID', 'BooksID', 'COLUMN';
The resulting output will be the same as that for the above queries. Now, that you have understood how to rename a column name in various databases, let us see how you can rename a table name.
Rename table name MySQL, MariaDB, Oracle
To rename a table name, you can use the RENAME command in SQL, in the following manner:
Syntax:
ALTER TABLE OldTableName RENAME TO NewTableName;
Example:
ALTER TABLE Books RENAME TO ListOfBooks;
Now, if you execute the below query to see the details in the table ListOfBooks, you will see the following output:
Query:
SELECT * FROM ListOfBooks;
| BooksID | BName | Genre | Price |
1 | Tricky Mirror | Psychology | 200 |
2 | Daisy Jones | Mystery | 350 |
3 | Lady in the lake | Mystery | 250 |
4 | Miracle Creek | Thriller | 450 |
5 | Disappearing Earth | Drama | 300 |
You will get more SQL queries interview questions here.
With that, we come to an end to this article on Rename column name in SQL. I hope you found this article informative. I hope you understood how to use the above commands. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this article on “Rename column name in SQL” and I will get back to you.