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!
136+ registered 
