{"id":8010,"date":"2019-08-29T20:04:10","date_gmt":"2019-08-30T03:04:10","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8010"},"modified":"2020-04-11T02:01:28","modified_gmt":"2020-04-11T09:01:28","slug":"mysql-alter-view","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-alter-view\/","title":{"rendered":"MySQL ALTER VIEW"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL ALTER VIEW<\/code> to modify the definition of an existing view.<\/p>\n\n\n\n

Introduction to MySQL ALTER VIEW statement<\/h2>\n\n\n\n

The MySQL ALTER VIEW<\/code> statement changes the definition of an existing view. The syntax of the ALTER VIEW<\/code> is similar to the CREATE VIEW<\/code> statement:<\/p>\n\n\n

ALTER<\/span>\n    [ALGORITHM = {UNDEFINED | MERGE<\/span> | TEMPTABLE}]\n    VIEW<\/span> view_name [(column_list)]\n    AS<\/span> select_statement;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL ALTER VIEW examples<\/h2>\n\n\n\n
See the following tables orders<\/code> and orderdetails<\/code> from the sample database<\/a>.<\/p>\n\n\n\n
\"orders<\/h3>\n\n\n\n
1) ALTER VIEW simple example<\/h3>\n\n\n\n
First, create a view<\/a> based on the orders<\/code> and orderdetails<\/code> tables:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> salesOrders AS<\/span>\n    SELECT<\/span> \n        orderNumber, \n        productCode,\n        quantityOrdered, \n        priceEach, \n        status<\/span>\n    FROM<\/span>\n        orders\n    INNER<\/span> JOIN<\/span>\n        orderDetails USING<\/span> (orderNumber);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SHOW<\/span> CREATE<\/span> VIEW<\/span> salesorders;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Third, use the ALTER VIEW<\/code> statement to change the processing algorithm of the view<\/a> from UNDEFINED<\/code> to MERGE<\/code> and add customerNumber<\/code> column to the view:<\/p>\n\n\n
ALTER<\/span> \n    ALGORITHM=MERGE<\/span>\nVIEW<\/span> salesOrders AS<\/span>\n    SELECT<\/span> \n        orderNumber, \n        customerNumber,\n        productCode,\n        quantityOrdered, \n        priceEach, \n        status<\/span>\n    FROM<\/span>\n        orders\n    INNER<\/span> JOIN<\/span>\n        orderDetails USING<\/span> (orderNumber);    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, show the view information to see the effect:<\/p>\n\n\n
SHOW<\/span> CREATE<\/span> VIEW<\/span> salesorders;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/h3>\n\n\n\n
2) ALTER VIEW using MySQL Workbench<\/h3>\n\n\n\n
Using MySQL Workbench to modify an existing view is more practical. Because you need to see the whole view definition before changing it.<\/p>\n\n\n\n
First, open the Views, right-click the view that you want to modify, and select Alter View…<\/em> menu item:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL Workbench will open an editor that displays the view’s DDL.<\/p>\n\n\n\n
Second, change the DDL of the view and click the Apply<\/em> button to confirm the changes. If you want to revert the change, click the Revert<\/em> button:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL Workbench opens a confirmation window.<\/p>\n\n\n\n
Third, click the Apply<\/em> button to apply the change.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Note that MySQL Workbench uses CREATE OR REPLACE VIEW<\/a><\/code> statement instead of ALTER VIEW<\/code> statement<\/p>\n\n\n\n
Finally, click the Finish<\/em> button to close the window.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the MySQL ALTER VIEW<\/code> statement to modify an existing view. In addition, you learned how to use MySQL Workbench to alter a view.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Second, query data<\/a> from the view salesorders<\/code>:<\/p>\n\n\n