How to Rename a Column in SQL

Renaming a column in SQL is a common task when managing databases. Whether you need clearer names, updated naming conventions, or to correct errors, knowing how to rename a column is essential. This guide will show you how to rename a column in SQL across different database systems. 

In this blog, you will learn step-by-step methods to rename columns in MySQL, SQL Server, Oracle, and PostgreSQL. You will see code examples, best practices, and how to avoid common mistakes while renaming columns. 

Want to strengthen your SQL and data skills? Explore our Online Data Science Courses and take the next step in your tech career! 

Two Main Approaches to Renaming Columns in SQL 

When you need to change a column's name, you first have to ask: is this change for a single query, or is it a permanent change to the database structure? SQL provides two different methods to handle these scenarios. 

Take your expertise beyond SQL and dive into cutting-edge fields like data science, AI, and generative AI with these top university-backed programs: 

• Master’s in Data Science – LJMU 
• Master’s Degree in Artificial Intelligence and Data Science– Jindal Global University 
• Executive Programme in Generative AI for Leaders – IIIT Bangalore 

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

1. Temporary Renaming (Aliasing): This approach changes a column's name only for the results of a specific query. The actual name of the column in the database table does not change. This is done using the AS keyword and is perfect for making reports and query outputs more readable. 
2. Permanent Renaming: This approach changes the column's name in the database schema itself. After this change, all future queries and application code must use the new column name. This is a structural change performed using the ALTER TABLE statement. 

Here is a quick comparison of the two methods: 

Temporary Renaming with the AS Keyword (Aliasing) 

The most frequent way you will rename a column is temporarily within a SELECT statement. This is called aliasing. An alias is a temporary, alternative name for a column or table that exists only for the duration of the query. It is a simple yet powerful tool for improving clarity. 

The primary reasons to use an alias are: 

• To make column names in your query output more descriptive and user-friendly. 
• To shorten long or complex column names for easier reference within the query. 
• To distinguish between columns that have the same name when performing a JOIN on multiple tables. 
• To provide a name for a calculated column. 

Syntax 

The syntax for creating a column alias is straightforward: 

SQL 
SELECT original_column_name AS new_column_name 
FROM table_name; 
 

In most SQL dialects, including PostgreSQL and Oracle, the AS keyword is optional, so you can also write: 

SQL 
SELECT original_column_name new_column_name 
FROM table_name; 
 

Examples of Aliasing 

Let's assume we have an employees table with the following structure and data: 

Example 1: Basic Column Renaming 

Let's make the column names in our query result more readable. 

SQL 
SELECT 
    emp_id AS "Employee ID", 
    first_name AS "First Name", 
    last_name AS "Last Name" 
FROM 
    employees; 
 

Result: 

Note: We use double quotes to create aliases with spaces. 

Example 2: Renaming a Calculated Column 

Aliases are essential for giving a name to a column that is generated from a calculation. 

SQL 
SELECT 
    first_name, 
    monthly_salary * 12 AS "Annual Salary" 
FROM 
    employees; 
 

Result: 

Without the alias, the second column would have a generic or system-generated name like column, which is not helpful. 

Also Read: SQL for Data Science: Functions, Queries, and Best Practices 

How to Rename a Column in SQL Permanently (ALTER TABLE) 

When you need to make a lasting change to your database structure, you will use the ALTER TABLE statement. This command modifies the definition of a table, and one of its functions is to rename columns. 

The exact syntax for how to rename a column in SQL permanently can differ between database management systems (DBMS). Below, we will cover the specific commands for four of the most popular systems: MySQL, PostgreSQL, SQL Server, and Oracle. 

Renaming a Column in MySQL 

MySQL provides two ways to rename a column: RENAME COLUMN and CHANGE. 

Method 1: Using RENAME COLUMN 

This is the preferred and more direct method. It only changes the name of the column. 

Syntax: 

SQL 
ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name; 
 

Example: 

Let's create a sample products table. 

SQL 
CREATE TABLE products ( 
    prod_id INT PRIMARY KEY, 
    prod_name VARCHAR(100), 
    price_usd DECIMAL(10, 2) 
); 
 

Now, let's rename the price_usd column to price. 

SQL 
ALTER TABLE products 
RENAME COLUMN price_usd TO price; 
 

The column is now permanently renamed. Any new query must use price. 

Method 2: Using CHANGE 

The CHANGE keyword can also rename a column, but it is more powerful and requires you to restate the column's data type and other attributes. 

Syntax: 

SQL 
ALTER TABLE table_name 
CHANGE old_column_name new_column_name column_definition; 
 

Example: 

Let's change prod_name to product_name and keep its data type. 

SQL 
ALTER TABLE products 
CHANGE prod_name product_name VARCHAR(100); 
 

You must provide the data type (VARCHAR(100)) again. Forgetting or changing this can lead to data loss, so RENAME COLUMN is generally safer for simple renaming tasks. 

Also Read: Difference Between VARCHAR and VARCHAR2 

Renaming a Column in PostgreSQL 

PostgreSQL follows the SQL standard for renaming columns, making its syntax very clean and intuitive. 

Syntax: 

SQL 
ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name; 
 

Example: 

Let's create a customers table and then rename a column. 

SQL 
CREATE TABLE customers ( 
    cust_id SERIAL PRIMARY KEY, 
    fname VARCHAR(50), 
    email_address VARCHAR(100) 
); 
 

Now, let's rename the fname column to first_name. 

SQL 
ALTER TABLE customers 
RENAME COLUMN fname TO first_name; 
 

The command is simple and directly communicates your intent, which is a key part of how to rename a column in SQL effectively. 

Also Read: MongoDB vs PostgreSQL: Key Differences, Similarities, and More 

Let’s check how to rename a column in SQL Server. 

Renaming a Column in SQL Server 

SQL Server uses a system stored procedure called sp_rename to rename database objects, including columns. 

Syntax: 

SQL 
EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN'; 
 

Example: 

Let's create an orders table and rename a column. 

SQL 
CREATE TABLE orders ( 
    order_id INT PRIMARY KEY, 
    order_dt DATE, 
    shipping_addr VARCHAR(255) 
); 
 

Now, let's rename order_dt to order_date. 

SQL 
EXEC sp_rename 'orders.order_dt', 'order_date', 'COLUMN'; 
 

The first parameter is the object to be renamed, formatted as 'TableName.OldColumnName'. The second is the new name, and the third parameter specifies that you are renaming a COLUMN. 

Also Read: Mastering SQL Server Management Studio (SSMS): A Comprehensive Guide 

After knowing how to rename a column in SQL Server, now explore how to rename for Oracle. 

Renaming a Column in Oracle 

Oracle also follows the standard SQL syntax for renaming columns, similar to PostgreSQL. 

Syntax: 

SQL 
ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name; 
 

Example: 

Let's create an inventory table and then perform a rename. 

SQL 
CREATE TABLE inventory ( 
    item_id NUMBER PRIMARY KEY, 
    item_desc VARCHAR2(200), 
    qty_on_hand NUMBER 
); 
 

Now, let's rename item_desc to item_description. 

SQL 
ALTER TABLE inventory 
RENAME COLUMN item_desc TO item_description; 
 

Summary Table for Quick Reference:

Here is a table summarizing how to rename a column in SQL across the different systems-

Potential Risks and Best Practices 

Permanently renaming a column is a significant change to your database schema and carries risks. It is not just about changing a name; it can have a ripple effect across your entire system. 

Impact on Dependencies 

The column you are renaming might be used by other database objects or by your application code. Changing the name will break these dependencies. 

• Views: Any views that select the column by its old name will stop working. 
• Stored Procedures and Functions: Any programmed logic inside the database that references the old column name will fail. 
• Application Code: Your application's source code (e.g., Python, Java, C#) that queries the column will throw errors. 
• Indexes and Constraints: While most systems automatically update indexes tied to a renamed column, it is a point to be aware of. 

Also Read: SQL Jobs for Freshers: Salary, Career Growth & Opportunities 

Best Practices Checklist 

To mitigate these risks, always follow a careful process: 

• Check for Dependencies: Before renaming, run queries against the system's metadata tables to find all views, stored procedures, and other objects that reference the column. 
• Communicate with Your Team: Inform application developers and other stakeholders about the planned change so they can update their code accordingly. 
• Use a Migration Script: Perform schema changes as part of a version-controlled database migration script. This makes the change trackable and repeatable. 
• Backup Your Data: Always perform a full backup of the database before making any structural changes. This is your safety net if something goes wrong. 
• Test in a Staging Environment: Never run a schema change command for the first time in a live production environment. Always test it on a development or staging server first. 

Conclusion 

Learning how to rename a column in SQL is more than just memorizing a command. It is about understanding the difference between temporary and permanent changes and managing the impact of those changes safely. With the commands and best practices outlined above, you are now prepared to manage your database schema with confidence. 

Discover our top Software Engineering courses and find the perfect program to advance your skills and career!

Unlock your potential with our free Software Development courses and start building skills for a thriving tech career!

Explore Our Software Development Free Courses

Master essential Software Development skills in demand and stay ahead in the dynamic tech industry!

In-Demand Software Development Skills

Dive into our popular articles on Software and stay informed about the latest trends and innovations in the field!

Read our Popular Articles related to Software

Discover popular SQL tutorials and enhance your database management skills to excel in your tech journey!

Explore Popular SQL Tutorials