← SQL roadmap

Topic 4 of 52 · Beginner

ALTER TABLE – RENAME COLUMN

Rename a column without touching its data or type.

01ALTER TABLE – RENAME COLUMN

Module 02 – Database & Table Management (DDL)

02What is it?

RENAME COLUMN changes only the name of a column. The data type, constraints and all stored values stay exactly the same.

03Why do we need it?

Teams adopt naming standards (snake_case, clear names), or a column name turns out misleading — e.g. salary actually stores monthly pay. Renaming fixes the meaning while keeping the data.

04Syntax

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

RENAME COLUMN: MySQL 8.0+, PostgreSQL, Snowflake, BigQuery all support it.

05Parameter explanation

old_name
Current column name; must exist.
new_name
New column name; must not already exist in the table.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL checks salary exists and annual_salary does not.
  2. 2.Only the metadata (column name) is updated — no data is rewritten.
  3. 3.Queries must now use annual_salary; SELECT salary will fail.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

When a source system renames a field (cust_nm → customer_name), the data engineer renames the matching staging column, then updates every downstream view and query that referenced the old name.

12Common mistakes

ALTER TABLE employees RENAME salary TO pay;

Without COLUMN, MySQL reads this as renaming the table.

ALTER TABLE employees RENAME COLUMN salary TO pay;

ALTER TABLE employees RENAME COLUMN salery TO pay;

Typo: column salery doesn't exist.

ALTER TABLE employees RENAME COLUMN salary TO pay;

ALTER TABLE employees RENAME COLUMN salary TO name;

name already exists → duplicate column.

Choose a unique new name, e.g. base_salary.

13Important comparisons

Command (MySQL)Renames?Changes type?
RENAME COLUMN a TO bYesNo
CHANGE COLUMN a b typeYesYes (must restate type)
MODIFY COLUMN a typeNoYes

14Practical exercises

Beginner1. Rename dept_name to name in departments.

Reveal solution (15)
Solution · run it

Allowed because departments has no other column called name.

Beginner2. Rename hire_date to start_date.

Reveal solution (15)
Solution · run it

Data is unchanged.

Beginner3. Rename emp_id to employee_id and show the first two rows.

Reveal solution (15)
Solution · run it

Primary key status carries over.

Intermediate4. Rename salary to monthly_salary and show it divided by 12 as monthly_pay.

Reveal solution (15)
Solution · run it

Aliases (AS) rename only in the output; RENAME COLUMN renames permanently.

Intermediate5. Rename two columns in employees: name → emp_name, dept_id → department_id.

Reveal solution (15)
Solution · run it

MySQL also allows both in one statement separated by commas.

Intermediate6. Show that the old name fails after renaming salary to pay.

Reveal solution (15)
Solution · run it

Expected error: no such column salary.

Advanced7. After renaming dept_id to department_id, join employees to departments.

Reveal solution (15)
Solution · run it

Every query that used the old name must be updated.

Advanced8. Rename name to full_name, then build a view on it.

Reveal solution (15)
Solution · run it

Rename before creating dependent views to avoid breaking them.

16Key takeaways

  • RENAME COLUMN changes only the name.
  • Available in MySQL 8.0+; older MySQL used CHANGE COLUMN.
  • Update dependent views and queries after renaming.

17Knowledge check

1. Which MySQL version introduced RENAME COLUMN?

2. Does RENAME COLUMN change stored values?

3. ALTER TABLE t RENAME x TO y (without COLUMN) in MySQL means…

Interview questions

RENAME COLUMN vs CHANGE COLUMN?

RENAME COLUMN only renames. CHANGE COLUMN renames and requires you to restate the full definition, so it can also change the type.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “ALTER TABLE – RENAME COLUMN” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.