SQL Rename Table

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.

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 –

1. SQL MOD()
2. SQL LAG()
3. SQL Compare String
4. SQL UPDATE Trigger