How to Change Column Name in SQL?

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.

The following topics will be covered in this article:

1. 1. What is SQL?
   2. What is RENAME command in SQL?
   3. Rename column name in MySQL, MariaDB, Oracle and PostgreSQL
   4. Rename column  name in MS SQL Server
   5. Rename table name  MySQL, MariaDB, Oracle

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:

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 ;

Now let us add one column to this existing table:

Syntax

ALTER TABLE Student

ADD dateOfBirth int;

Output;

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 :

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:

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;

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.