MySQL Rename View

Summary: in this tutorial, you will learn how to rename a view in MySQL using the RENAME TABLE statement or a sequence of DROP VIEW and CREATE VIEW statements.

Introduction to the RENAME TABLE statement

In MySQL, views and tables share the same namespace. Therefore, you can use the RENAME TABLE statement to rename a view.

Here’s the basic syntax of the RENAME TABLE for renaming a view:

RENAME TABLE original_view_name 
TO new_view_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the view’s name that you want to rename after the RENAME TABLE keywords.
• Then, specify the new name of the view after the TO keyword.

Note that you cannot use the RENAME TABLE statement to move a view from one database to another. If you attempt to do so, MySQL will issue an error.

Another indirect way to rename a view is to use a sequence of the DROP VIEW and CREATE VIEW statement.

• First, get the CREATE VIEW statement by using the SHOW CREATE VIEW statement.
• Next, copy the CREATATE VIEW statement and save it to a file.
• Then, drop the view using the DROP VIEW statement.
• After that, change the name of the view in the CREATE VIEW statement.
• Finally, execute the CREATE VIEW statement to create the view with the new name.

Note that by using a sequence of DROP VIEW and CREATE VIEW statements, you can also move a view from one database to another.

MySQL Rename View examples

Let’s take some examples of renaming a view.

1) Renaming a view using the RENAME TABLE statement example

First, create a new view called productLineSales for the demonstration:

CREATE VIEW productLineSales AS
SELECT 
    productLine, 
    SUM(quantityOrdered) totalQtyOrdered
FROM
    productLines
        INNER JOIN
    products USING (productLine)
        INNER JOIN
    orderdetails USING (productCode)
GROUP BY productLine;Code language: SQL (Structured Query Language) (sql)

Second, rename the view productLineSales to productLineQtySales:

RENAME TABLE productLineSales 
TO productLineQtySales;Code language: SQL (Structured Query Language) (sql)

Third, use the SHOW FULL TABLES to check if the view has been renamed successfully:

SHOW FULL TABLES WHERE table_type = 'VIEW';Code language: SQL (Structured Query Language) (sql)

2) Renaming a view using the DROP VIEW and CREATE VIEW sequence example

Suppose you want to change the name of the view productLineQtySales to categorySales.

First, use the SHOW CREATE VIEW to get the view’s definition:

SHOW CREATE VIEW productLineQtySales;Code language: SQL (Structured Query Language) (sql)

Here is the output:

Note that you should copy the statement in the Create View column.

Second, drop the view productLineQtySales:

DROP VIEW productLineQtySales;Code language: SQL (Structured Query Language) (sql)

Third, change the name of the view the CREATE VIEW statement and execute it:

CREATE VIEW categorySales AS
SELECT 
    productLine, 
    SUM(quantityOrdered) totalQtyOrdered
FROM
    productLines
        INNER JOIN
    products USING (productLine)
        INNER JOIN
    orderDetails USING (productCode)
GROUP BY productLine;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the RENAME TABLE statement to rename a view.