MySQL Rename View

Rename View Tutorial

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)
Display Views
Display Views

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)
Rename View
Rename View

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)
Show Create View
Show Create View

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='VIEW
Code language: SQL (Structured Query Language) (sql)
Rename View Using Combinations
Rename View Using Combinations

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!

Further reading