{"id":7900,"date":"2019-08-28T17:45:44","date_gmt":"2019-08-29T00:45:44","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7900"},"modified":"2021-07-23T19:29:54","modified_gmt":"2021-07-24T02:29:54","slug":"mysql-drop-view","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-drop-view\/","title":{"rendered":"MySQL DROP VIEW"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DROP VIEW<\/code> statement to delete a view from the database.<\/p>\n\n\n\n

Introduction to the MySQL DROP VIEW statement<\/h2>\n\n\n\n

The DROP VIEW<\/code> statement deletes a view<\/a> completely from the database. Here’s the basic syntax of the DROP VIEW<\/code> statement:<\/p>\n\n\n

DROP<\/span> VIEW<\/span> [IF<\/span> EXISTS<\/span>] view_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the view that you want to drop after the DROP VIEW<\/code> keywords. The optional IF EXISTS<\/code> option conditionally removes the view only if it exists.<\/p>\n\n\n\n
To remove multiple views in a single statement, you use the following syntax:<\/p>\n\n\n
DROP<\/span> VIEW<\/span> [IF<\/span> EXISTS<\/span>] view_name1 [,view_name2]...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify a list of comma-separated views after the DROP VIEW keywords. <\/p>\n\n\n\n
If the list contains a view that doesn’t exist, the DROP VIEW<\/code> statement will fail and won’t delete any view. However, if you use the IF EXISTS<\/code> option, the DROP VIEW<\/code> statement will generate a NOTE<\/code> for each non-existing view.<\/p>\n\n\n\n
Note that in MySQL 5.7 or earlier, the DROP VIEW<\/code> returns an error if there is any non-existing view. However, it drops the views that exist.<\/p>\n\n\n\n
MySQL DROP VIEW statement examples<\/h2>\n\n\n\n
Let’s take some examples of using the DROP VIEW<\/code> statement.<\/p>\n\n\n\n
1) MySQL DROP VIEW – drop a view example<\/h3>\n\n\n\n
This statement creates a view<\/a> named customerPayments<\/code> based on the customers<\/code> and payments<\/code> tables:<\/p>\n\n\n\n
\"customers<\/figure>\n\n\n
CREATE<\/span> VIEW<\/span> customerPayments \nAS<\/span>\n    SELECT<\/span> \n        customerName, \n        SUM<\/span>(amount) payment\n    FROM<\/span>\n        customers\n    INNER<\/span> JOIN<\/span> payments \n        USING<\/span> (customerNumber)\n    GROUP<\/span> BY<\/span> \n        customerName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example uses the DROP VIEW<\/code> statement to drop the customerPayments<\/code> view:<\/p>\n\n\n
DROP<\/span> VIEW<\/span> IF<\/span> EXISTS<\/span> customerPayments;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) MySQL DROP VIEW – drop multiple views example<\/h3>\n\n\n\n
This statement creates a view<\/a> named employeeOffices<\/code> based on the employees<\/code> and offices<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
CREATE<\/span> VIEW<\/span> employeeOffices AS<\/span>\n    SELECT<\/span> \n        firstName, lastName, addressLine1, city\n    FROM<\/span>\n        employees\n            INNER<\/span> JOIN<\/span>\n        offices USING<\/span> (officeCode);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the DROP VIEW<\/code> statement to delete two views employeeOffices<\/code> and eOffices<\/code>:<\/p>\n\n\n
DROP<\/span> VIEW<\/span> employeeOffices, eOffices;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error Code: 1051. Unknown table 'classicmodels.eoffices'\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s add the IF EXISTS<\/code> option like this:<\/p>\n\n\n
DROP<\/span> VIEW<\/span> IF<\/span> EXISTS<\/span> employeeOffices, eOffices;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued a warning instead:<\/p>\n\n\n
1 warning(s): 1051 Unknown table 'classicmodels.eoffices'\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The employeeOffices<\/code> view remains intact.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
CREATE<\/span> VIEW<\/span> productCatalogs AS<\/span>\n    SELECT<\/span> \n        productLine, productName, msrp\n    FROM<\/span>\n        products\n            INNER<\/span> JOIN<\/span>\n        productLines USING<\/span> (productLine);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the DROP VIEW<\/code> statement to delete the employeeOffices<\/code> and productCatalogs<\/code> views:<\/p>\n\n\n
DROP<\/span> VIEW<\/span> employeeOffices, productCatalogs;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL deleted the views completely.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
  • Use the DROP VIEW<\/code> statement to delete one or more views from a database.<\/li>
  • Use the IF EXISTS<\/code> option to conditionally delete a view if it exists.<\/li><\/ul>\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
This statement creates a new view<\/a> named productCatalogs<\/code> based on the products<\/code> and productLines<\/code> tables:<\/p>\n\n\n\n