How to Rename a Column Using ALTER TABLE in SQL

August 8, 2024 By Matthew Rathbone

MySQL

For MySQL, it’s important to note that the syntax is a bit different. MySQL doesn’t directly support ALTER TABLE RENAME COLUMN. However, we can use the CHANGE keyword. Here’s how it’s done:

ALTER TABLE table_name
CHANGE old_column_name new_column_name column_type;

An actual implementation looks like this:

ALTER TABLE Employees
CHANGE Name Fullname VARCHAR(100);

In this code, we’re changing the ‘Name’ column in the ‘Employees’ table to ‘Fullname.’ Note that you’ll need to specify the column type.

PostgreSQL and Oracle SQL

In PostgreSQL and Oracle SQL, you can directly use the ALTER TABLE... RENAME COLUMN syntax, as shown below:

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

An example would be:

ALTER TABLE Employees
RENAME COLUMN Name TO Fullname;

SQL Server

SQL Server also does not directly support the ALTER TABLE... RENAME COLUMN syntax. We will have to use the stored procedure sp_rename. This procedure accepts the full name of the table and column, the new name for the column, and a hard-coded string ‘COLUMN’:

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

Here is how to use the sp_rename stored procedure:

EXEC sp_rename 'Employees.Name', 'Fullname', 'COLUMN';

Sqlite

SQLite also supports the ALTER TABLE... RENAME COLUMN syntax.

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

For example:

ALTER TABLE Employees
RENAME COLUMN Name TO Fullname;

Renaming Columns Using Beekeeper Studio

Beekeeper Studio works with a variety of SQL databases such as MySQL, PostgreSQL, SQL Server, and SQLite.

To rename a column, simply right-click the column you wish to rename in the schema view, select ‘Rename column,’ and then enter the new name.

Conclusion

Renaming columns is a straightforward process with the ALTER TABLE statement in SQL. The syntax varies slightly between different SQL databases, but the concept remains the same. Make sure to test your changes in a development environment before applying them to production to avoid any disruptions. Happy coding!

Other articles you may enjoy: