MySQL ALTER VIEW

Summary: in this tutorial, you will learn how to use the MySQL ALTER VIEW to modify the definition of an existing view.

Introduction to MySQL ALTER VIEW statement

The MySQL ALTER VIEW statement changes the definition of an existing view. The syntax of the ALTER VIEW is similar to the CREATE VIEW statement:

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement;
Code language: SQL (Structured Query Language) (sql)

MySQL ALTER VIEW examples

See the following tables orders and orderdetails from the sample database.

1) ALTER VIEW simple example

First, create a view based on the orders and orderdetails tables:

CREATE VIEW salesOrders AS
    SELECT 
        orderNumber, 
        productCode,
        quantityOrdered, 
        priceEach, 
        status
    FROM
        orders
    INNER JOIN
        orderDetails USING (orderNumber);
Code language: SQL (Structured Query Language) (sql)

Second, query data from the view salesorders:

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

Third, use the ALTER VIEW statement to change the processing algorithm of the view from UNDEFINED to MERGE and add customerNumber column to the view:

ALTER 
    ALGORITHM=MERGE
VIEW salesOrders AS
    SELECT 
        orderNumber, 
        customerNumber,
        productCode,
        quantityOrdered, 
        priceEach, 
        status
    FROM
        orders
    INNER JOIN
        orderDetails USING (orderNumber);    
Code language: SQL (Structured Query Language) (sql)

Finally, show the view information to see the effect:

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

2) ALTER VIEW using MySQL Workbench

Using MySQL Workbench to modify an existing view is more practical. Because you need to see the whole view definition before changing it.

First, open the Views, right-click the view that you want to modify, and select Alter View… menu item:

MySQL Workbench will open an editor that displays the view’s DDL.

Second, change the DDL of the view and click the Apply button to confirm the changes. If you want to revert the change, click the Revert button:

MySQL Workbench opens a confirmation window.

Third, click the Apply button to apply the change.

Note that MySQL Workbench uses CREATE OR REPLACE VIEW statement instead of ALTER VIEW statement

Finally, click the Finish button to close the window.

In this tutorial, you have learned how to use the MySQL ALTER VIEW statement to modify an existing view. In addition, you learned how to use MySQL Workbench to alter a view.