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 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 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:
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 have created earlier: –
ALTER TABLE borrower_data
DROP COLUMN loan_type;
Note: Some databases will not allow dropping the columns from the tables.
Conclusion
- We user ‘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 to drop 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
This is a guide to SQL Rename Table. Here we discuss the introduction, syntax, parameters, and examples with code implementation respectively. You may also have a look at the following articles to learn more –
