In this tutorial, we will see how can we rename a MySQL view using the RENAME TABLE statement. Along with it, we will see another way also to rename a view in MySQL. So, without wasting any time, let’s get started!
Introduction to MySQL Rename Views
In MySQL, views are considered the same as tables. Hence, you can change the name of views using the RENAME TABLE statement.
Quick note- Views are the same as the table but with the table type ‘VIEW’. The tables which are created using CREATE TABLE statement have the type ‘BASE TABLE’.
Rename views using the RENAME TABLE
Syntax of renaming a view using the RENAME TABLE statement is –
RENAME TABLE view_name
TO new_view_name;Code language: SQL (Structured Query Language) (sql)Note that, you can not move the view from one database to another using the RENAME TABLE statement. Migration using the RENAME TABLE only works for the BASE TABLES.
Using a Combination of DROP VIEW and CREATE VIEW
There is another way to rename a view apart from the RENAME TABLE statement which is by using the combination of the DROP VIEW and CREATE VIEW statements.
Note that, this is not a recommended method to rename the view. However, it would add some ideas to you on how one task can be accomplished by different methods.
Here is the step-by-step guide to renaming a view –
- First, execute the SHOW CREATE VIEW view_name statement and you will get the view information.
- Now Copy the CREATE VIEW statement and store it somewhere.
- Then drop the view using the DROP VIEW statement.
- Finally, change the name of the view in the create view statement and execute it.
- A new view with a new name will be created.
Note that, if you want to move a view from one database to another, this method is fine to use.
MySQL Rename View Examples
Using the RENAME TABLE Statement
Here, we have a view called ‘getData’. You can list all views using the SHOW FULL TABLES statement.
SHOW FULL TABLES
WHERE TABLE_TYPE='VIEW';Code language: SQL (Structured Query Language) (sql)
Here, we have only one view in our database. It is because we will immediately understand when we rename a view later in this tutorial.
For more information about displaying views, you can read our detailed guide here.
We will change the name of a view ‘getData’ to ‘fullData’ using the RENAME TABLE statement.
RENAME TABLE getData
TO fullData;Code language: SQL (Structured Query Language) (sql)The above query will change the name of the ‘getData’ view. Let’s run the SHOW FULL TABLES query to check if the view name is changed.
SHOW FULL TABLES
WHERE TABLE_TYPE='VIEW';Code language: SQL (Structured Query Language) (sql)
As you can see, the view name is changed.
Using a Combination of DROP VIEW and CREATE VIEW
In the previous example, we changed the view name to ‘fullData’. In this example, we will rename ‘fullData’ view to ‘fetchData’.
First, we have to get the create view statement using the SHOW CREATE VIEW statement.
SHOW CREATE VIEW fullData \G;Code language: SQL (Structured Query Language) (sql)
Here, we have to copy the highlighted data.
Now drop the view using the DROP VIEW statement.
DROP VIEW fullData;Code language: SQL (Structured Query Language) (sql)Now, change the name of the view to ‘fetchData’ in the statement that we copied earlier and execute it.
Note that, if you are worried about the extra backtick symbols (`), leave them as it is. It won’t bother you while executing the query.
The new query will look like this –
CREATE VIEW fetchData AS
select `emps`.`id` AS `id`,`emps`.`name` AS `name`,`emps`.`city` AS `city`
from `emps`;Code language: SQL (Structured Query Language) (sql)SHOW FULL TABLES WHERE TABLE_TYPE='VIEWCode language: SQL (Structured Query Language) (sql)
As you can see, the view name is changed to ‘fetchdata’.
Summary
In this tutorial, we have learned how to rename a view using two methods- using the RENAME TABLE statement and using the combination of the DROP VIEW and CREATE VIEW statements. Stay tuned for more interesting and helpful tutorials!