AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (14 Blogs)
  • MySQL (41 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

How To Rename a Column Name in SQL?

Published on Sep 19,2019 78 Views
20 / 30 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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.

SQL-Rename Column Name in SQL-EdurekaThe following topics will be covered in this article:

    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:

BIDBNameGenrePrice

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

Rename column name in SQL

Rename 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;

On executing the above query, you will see the following output:

Output:

BooksIDBNameGenrePrice

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.

Rename column name in MS 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;
BooksIDBNameGenrePrice

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

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.

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.