SQL Rename column example- Learn how to modify column name of a table in MySQL database with examples when there is either no data or no valuable data and if there is a valid data. Renaming a column of a table means, changing the name of the column without changing the datatype and size.
SQL Rename column – If no valid data
If the column of a table has no data or no valuable data, then you remove / drop the column and then add a new column to table. Below is the syntax for this operation. Recommended to read in detail how to add new column in SQL table.
SQL Syntax
ALTER TABLE [table_name]
DROP [COLUMN_NAME],
ADD [COLUMN_NAME] [data_type] [FIRST | AFTER [COLUMN_NAME]]
SQL Rename table query example – If no data exists
Consider a table BOOKS from which we can drop a column e.g. PRICE and add a new column if the data is not valuable or there is no data exists in the specified PRICE column.
In below query, we are dropping the column PRICE from books table and adding a new column named BOOK_PRICE after AUTHORLASTNAME.
Query example:
ALTER TABLE BOOKS
DROP PRICE ,
ADD BOOK_PRICE FLOAT AFTER AUTHORLASTNAME;
SQL Rename a column – If valid data exists
This is the sql rename column query if we want to change existing column in which valid data exists.
Syntax:
ALTER TABLE [table_name]
CHANGE [OLD_COLUMN_NAME] [NEW_COLUMN_NAME] [DATATYPE];
SQL Rename column example with valid data
In this example, we are altering the table and modifying the existing PRICE column of table BOOKS.
Query example:
ALTER TABLE BOOKS
CHANGE PRICE BOOK_PRICE FLOAT;