Updated July 5, 2023
Introduction to SQL Rename Table
In order to make changes in the names or columns of a table for an appropriate business understandable name, we get the concept ‘RENAME.’ We use ‘ALTER’ command to rename the table, rename/ modify the column, drop the column, and add the column to the table.
Let’s see the commands to add a column, rename or modify a column, rename the table name, and drop the column.
ADD column to the table:
ALTER TABLE < table_name >
ADD < column_name > < data_type > ; - - Adding the column to table - - - -
DROP column to the table:
ALTER TABLE < table_name >
DROP COLUMN < column_name > ; - - Dropping the column to table - - - -
RENAME column to the table:
ALTER TABLE < table_name >
RENAME COLUMN <old_column_name> TO < new_column_name >;
- - Rename the existing column to new column name in the table - - - -
RENAME table name to the new table:
ALTER TABLE < table_name >
RENAME TO < new_tablename > ; - - Rename the column to table - - -
Syntax:
ALTER TABLE < table_name >
RENAME TO < new_tablename > ; - - Syntax to rename the table to new table name - - -
How to RENAME the Table in SQL?
Let us consider the tables below and apply the RENAME on it: – Below is the table name with ‘Loan’ before renaming it to ‘loan_data’ table
Loan Table:
Select * from Loan;
Now let us ‘RENAME’ the table to ‘LOAN_DATA’.
ALTER TABLE LOAN
RENAME TO LOAN_DATA ;
Now let’s select the table with the old name ‘Loan.’
select * from LOAN;
We will see there is no such table existing error. Below is the screenshot for the above:
SELECT * FROM LOAN_DATA;
Below is the screenshot for the above:
Examples
Let us consider a few examples.
- Borrower Table:
Select * from Borrower;
ALTER TABLE BORROWER
RENAME TO BORROWER _DATA ;
- ADD column to the table:
ALTER TABLE < table_name >
ADD < column_name > < data_type > ;
Now let’s add a column to the above ‘BORROWER_DATA’ table.
ALTER TABLE BORROWER_DATA
ADD LOAN_TYPE VARCHAR(20) ;
Below is the screenshot for the above:
SELECT * FROM BORROWER_DATA;
Below is the screenshot for the above:
Now let’s insert data into the ‘loan_type’ column.
insert into borrower_data values (24,'Suppe','12-09-2019','A2','Premium');
insert into borrower_data values (25,'Sam','18-05-2019','A1','Fast Track');
insert into borrower_data values (98,'Ashok','13-01-2018','A3','Fast Track');
insert into borrower_data values (60,'Ramya','15-10-2014','A4','Standard');
select * from borrower_data;
Output:
Below is the screenshot for the above:
- RENAME column to the table:
ALTER TABLE < table_name >
RENAME COLUMN <old_column_name> TO < new_column_name >;
Now let us rename the column ‘Borrower_name’ to ‘Borrower_first_name’
ALTER TABLE Borrower_data
RENAME COLUMN borrower_name to Borrower_first_name ;
Below is the screenshot for the above:
The output of the below query before renaming the column ‘borrower_name’. Below is the screenshot for the above:
select * from Borrower_data;
Here you can see that the column name has been changed to ‘borrower_first_name’ after renaming the column name.
select * from Borrower_data;
Below is the screenshot for the above:
- DROP column to the table:
ALTER TABLE < table_name >
DROP COLUMN < column_name > ;
Now let’s drop the column ‘Loan_type’, which we created earlier: –
ALTER TABLE borrower_data
DROP COLUMN loan_type;
Note: Some databases will not allow dropping the columns from the tables.
Conclusion
- We use the ‘Alter’ command for performing the operations of renaming the table and column, dropping the column, and adding the column to the table.
- We need to be careful while dropping the column from the table.
- We need to keep in mind that not all the databases will allow dropping the columns from the table.
- We get the concept of ‘RENAME’. We use ‘ALTER’ command to rename the table, rename/ modify the column, drop the column, and add the column to the table.
Recommended Articles
We hope that this EDUCBA information on “SQL Rename Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.