Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is SQL?
3.
What is RENAME command in SQL?
4.
Methods to Rename a Column in SQL
4.1.
1. Using RENAME keyword
4.1.1.
Syntax:
4.1.2.
Parameters:
4.2.
2. Using CHANGE keyword
4.2.1.
Syntax:
4.2.2.
Parameters:
4.3.
3. Using SP_RENAME keyword
4.3.1.
Syntax:
5.
Example of Renaming Column Names in SQL
5.1.
1. Using ALTER along with the RENAME Command
5.2.
2. Rename a Column using ALTER with the CHANGE Command
5.3.
3. Example to show RENAME Column name in SQL Server
6.
Exceptions while Renaming Column Names in SQL
6.1.
1. Column does not exist
6.2.
2. Duplicated column name
6.3.
3. Permission-related exceptions
6.4.
4. Index Constraints
6.5.
5. Foreign key constraint problem
7.
Frequently Asked Questions
7.1.
How do you rename a column name in SQL?
7.2.
Which keyword is used to rename a column in SQL?
7.3.
Can we rename the column name in Oracle SQL?
7.4.
Which operator is used to rename a column name in SQL query?
7.5.
Which operator is used to rename a column name in SQL query?
8.
Conclusion
Last Updated: Mar 27, 2024
Easy

How to Rename Column Name in SQL?

Author Ravi Khorwal
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Renaming operation refers to the process where we change the name of an existing entity to a new name. Renaming operation is very frequently required while working with databases. SQL provides us with efficient ways to rename database names, table names, column names, etc. In this article, we will explore in depth how to rename a column name in SQL. Without further ado, let's get started!

rename column name in sql

What is SQL?

SQL is Structured Query Language. It is a programming language used for handling and operating relational databases. SQL permits users to interact with databases in an organized way. It encourages people to save, recover, update, and delete data more systemically. SQL uses primary activities such as generating, inserting, editing, and deleting. These are only a few examples of database operations. There are plenty of others.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is RENAME command in SQL?

The rename command renames an existing database object (such as a Table or Column). While renaming a table, there is no data loss. There is a special syntax that we have to follow while working with the RENAME keyword. To use the RENAME command in SQL, begin with the keyword "RENAME," then enter the selected new name for a substitute, pursued by the current name of the table or column.

Also read, Natural Join in SQL

Methods to Rename a Column in SQL

This section will discuss various methods to rename column name in SQL.

1. Using RENAME keyword

The ALTER TABLE command in SQL is used to rename column name in SQL. The following is syntax for renaming a column in SQL is as follows:

Syntax:

ALTER TABLE tableName
RENAME COLUMN oldColumnName TO newColumnName;


The syntax mentioned above applies to MySQL, Oracle, and Postgres SQL.

Parameters:

The parameters used in renaming a column in SQL are:-

  • tableName: It denotes the table name whose column needs to be renamed.
     
  • oldColumnName:  This parameter denotes the current name of the column that needs to be renamed.
     
  • newColumnName: It denotes the new name for the column to be renamed.

2. Using CHANGE keyword

The second way to rename column name in SQL is by using the CHANGE keyword. This method is very similar to the RENAME method. The following syntax uses the CHANGE keyword to rename column name in SQL.

Syntax:

ALTER TABLE tableName
CHANGE COLUMN oldColumnName TO newColumnName;


The command mentioned above applies to both MySQL and MariaDB.

Parameters:

The parameters used in renaming a column in SQL are:-

  • tableName: It denotes the table name whose column needs to be renamed.
     
  • oldColumnName:  This parameter denotes the current name of the column that needs to be renamed.
     
  • newColumnName: It denotes the new name for the column to be renamed.

3. Using SP_RENAME keyword

This method is used to rename column name in SQL Server. The following syntax uses the SP_RENAME keyword to rename column name in SQL.

Syntax:

sp_rename 'tableName.oldColumnName', 'newColumnName', 'COLUMN';


The syntax mentioned above applies to MySQL, Oracle, and Postgres SQL.

Example of Renaming Column Names in SQL

1. Using ALTER along with the RENAME Command

You can use the ALTER TABLE command along with the RENAME command like the following syntax:-

ALTER TABLE your_table_name
RENAME COLUMN old_column_name TO new_column_name;

 

After executing this SQL command, the old_column_name will change to new_column_name. The rename command is available in Oracle SQL.

2. Rename a Column using ALTER with the CHANGE Command

In MySQL, you have to use the CHANGE command to rename column names for a table. Let's look at the syntax.

ALTER TABLE your_table_name
CHANGE old_name new_name VARCHAR(255);

 

Here, VARCHAR(255) represents the new data type and size for the "new_name" column. 

3. Example to show RENAME Column name in SQL Server

In SQL Server, you can use the sp_rename system stored procedure to rename a column in a table. Let's look at the syntax.

EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';

 

Here, 'COLUMN' specifies that you are renaming a column, as this command can also be used for naming tables and indexes.

Must Read ping command in linux

Exceptions while Renaming Column Names in SQL

The following are some exceptions that might arise when renaming column names in SQL:

1. Column does not exist

SQL will throw an exception if you try to rename a column that does not exist in your table.

2. Duplicated column name

If we try to rename a column to a column name that is already present in the table, it will lead to an exception.

3. Permission-related exceptions

SQL will throw an exception if the user tries to perform the rename operation without having modify permissions on the database/table.

4. Index Constraints

If the column we are trying to rename is part of an index, then the operation will create a problem. The solution is to drop the index, rename the column and recreate the index.

5. Foreign key constraint problem

If the column being renamed is part of a foreign key constraint, then the foreign key constraint needs to be dropped before renaming. Otherwise, it will lead to an exception.

Frequently Asked Questions

How do you rename a column name in SQL?

There are different ways in which we can rename a column name in SQL like using RENAME keyword or CHANGE keyword. We can also use SP_RENAME keyword to change the name of the column in SQL.

Which keyword is used to rename a column in SQL?

In SQL, the keyword used to rename a column is "ALTER TABLE." You can use the ALTER TABLE statement with the RENAME COLUMN clause to change the name of a specific column in an existing table.

Can we rename the column name in Oracle SQL?

Yes, in Oracle SQL, you can rename a column using the ALTER TABLE statement with the RENAME COLUMN clause to change the name of an existing column in a table.

Which operator is used to rename a column name in SQL query?

In SQL, the AS keyword is used to rename a column name in a query. It allows you to provide an alias for a column, making the result set more readable or meaningful.

Which operator is used to rename a column name in SQL query?

In SQL queries, the AS keyword is used to rename a column name, providing a temporary alias that exists for the duration of the query. This aliasing enhances readability and clarity in the presentation of query results.

Conclusion

Congratulations, you did a fantastic job!! This article has gone through the many amazing topics such as what is need for renaming column names, ways to rename column in SQL such as rename, change, sp_name. We even discuss the syntax for writing SQL queries to rename column names. In the end we come across examples to understand renaming of column.

We hope this blog has helped you enhance your knowledge. Do not stop learning! We recommend you read some of our SQL articles: 

We wish you Good Luck! 

Happy Learning!

Previous article
SQL Minus Operator
Next article
How to Change Column Name in SQL?
Live masterclass