MySQL DROP VIEW

Sometimes you may need to drop view in MySQL if you don’t need it anymore. Here’s how to drop view in MySQL using MySQL DROP VIEW command. You can use it to delete views from database.

How to Drop View in MySQL

Here are the steps to drop view in MySQL using MySQL DROP VIEW statement.

Here’s the syntax of MySQL DROP VIEW query.

DROP VIEW [IF EXISTS] view_name;

In the above query, you need to specify the name of your view in place of view_name

IF EXISTS is an optional keyword used to delete view only if it exists

Let’s say you have the following view.

mysql> create view order_view as
       select * from orders;

mysql> select * from order_view;
+----+--------------+------------+-------+-------------+---------------------+
| id | product_name | order_date | price | description | created_at          |
+----+--------------+------------+-------+-------------+---------------------+
|  1 | A            | 2020-07-01 |   150 | New product | 2020-06-01 00:00:00 |
|  2 | B            | 2020-07-01 |   235 | Old product | 2020-06-15 00:00:00 |
+----+--------------+------------+-------+-------------+---------------------+

Here’s the SQL query to drop view in MySQL.

mysql> drop view order_view;

mysql> select * from order_view;
ERROR 1146 (42S02): Table 'sample.order_view' doesn't exist

Bonus Read : How to Create Index in MySQL

MySQL DROP VIEW IF EXISTS

Here’s an example of DROP VIEW IF EXISTS. Let’s say you try to delete a non-existent view order_view

mysql> drop view order_view;
ERROR 1051 (42S02): Unknown table 'sample.order_view'

Now let’s try deleting the same statement with IF EXISTS

mysql> drop view if exists order_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Bonus Read : How to Create Stored Procedure in MySQL

How to Drop multiple views in MySQL

Here’s the syntax to drop multiple views in MySQL

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;

In the above query, you need to specify all the views you want to delete, in a comma-separated manner.

However, if any of the view doesn’t exist in above query, then the statement will fail and throw an error. That is why, it is useful to mention IF EXISTS keyword.

In that case, MySQL will generate a note for each non-existent view, instead of giving an error, and continue with the query execution.

Bonus Read : How to Create User in MySQL

Here’s an example to drop multiple views in MySQL

mysql> DROP VIEW employee_view, order_view;

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.