|
SQL > ALTER TABLE >
Rename Column Syntax
Sometimes we want to change the name of a column. To do this in SQL, we specify that we want to change the structure of the table using the ALTER TABLE command, followed by a command that tells the relational database that we want to rename the column. The exact syntax for each database is as follows:
ALTER TABLE RENAME COLUMN renames a column without affecting its data or data type. The syntax differs across databases — Oracle supports RENAME COLUMN directly, while MySQL uses CHANGE and SQL Server requires the sp_rename stored procedure.
In MySQL, the SQL syntax for ALTER TABLE Rename Column is,
ALTER TABLE "table_name"
Change "column 1" "column 2" ["Data Type"];
In Oracle, the syntax is,
ALTER TABLE "table_name"
RENAME COLUMN "column 1" TO "column 2";
Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:
Table Customer
| Column Name | Data Type |
| First_Name | char(50) |
| Last_Name | char(50) |
| Address | char(50) |
| City | char(50) |
| Country | char(25) |
| Birth_Date | datetime |
To rename "Address" to "Addr", we key in,
MySQL:
ALTER TABLE Customer CHANGE Address Addr char(50);
Oracle:
ALTER TABLE Customer RENAME COLUMN Address TO Addr;
SQL Server:
It is not possible to rename a column using the ALTER TABLE statement in SQL Server. Use sp_rename instead.
The resulting table structure is:
Table Customer
| Column Name | Data Type |
| First_Name | char(50) |
| Last_Name | char(50) |
| Addr | char(50) |
| City | char(50) |
| Country | char(25) |
| Birth_Date | datetime |
To rename a column in SparkSQL or Hive SQL, we would use the ALTER TABLE Change Column command.
Frequently Asked Questions
- Q: How do I rename a column in SQL Server?
- A: SQL Server does not support renaming a column via ALTER TABLE. Instead, use the system stored procedure:
EXEC sp_rename 'Customer.Address', 'Addr', 'COLUMN';
- Q: Does renaming a column affect the data in the column?
- A: No. Renaming a column only changes the column's name — the data, data type, and constraints remain unchanged.
- Q: What is the difference between RENAME COLUMN and CHANGE COLUMN?
- A: RENAME COLUMN (Oracle) only renames the column. CHANGE COLUMN (MySQL, SparkSQL, HiveQL) renames the column and can also change the data type at the same time — making it more flexible but requiring the data type to always be specified.
- Q: Will renaming a column break existing queries or views?
- A: Yes, potentially. Any queries, views, stored procedures, or application code that reference the old column name will break after the rename. Always search your codebase for the old column name before renaming.
Next: SQL DROP COLUMN
This page was last updated on March 19, 2026.
Copyright © 2026 1keydata.com All Rights Reserved
Privacy Policy About Contact |