SQL - DROP or DELETE View

The SQL DROP View statement is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also use this statement to drop indexed views.

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped exppcitly using the DROP VIEW command.

Note −

While trying to perform queries, the database engine checks all the objects referenced in that statement are vapd and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.

To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

Syntax

The basic syntax of this DROP View statement is as follows −

DROP VIEW view_name;

Example

Assume we have created a table named Customers using the CREATE TABLE statement using the following query −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

Assume we have created 3 views using the CREATE VIEW statement as shown below −

Create view first_view AS SELECT * FROM customers;
Create view second_view AS SELECT * FROM customers;
Create view third_view AS SELECT * FROM customers;

You can verify the pst of all the views using the following query −

SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| first_view     | VIEW       |
| second_view    | VIEW       |
| third_view     | VIEW       |
+----------------+------------+

Now, lets drop the above created views using the DELETE VIEW statement.

DROP VIEW first_view;
DROP VIEW second_view;

Verification

Once we have deleted all the views if you try to retrieve the pst of views you will get an empty set as shown below −

SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| third_view     | VIEW       |
+----------------+------------+

The IF EXISTS clause

Instead of always checking if the view exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP VIEW statement.

This clause, when specified in the DROP VIEW query, will automatically check whether the view exists in the current database and then drops it, if yes. If the view does not exist in the database, the query will be ignored.

Syntax

Following is the basic syntax of DROP VIEW IF EXISTS −

DROP VIEW [IF EXISTS] view_name;

Example

If you try to drop a view that doesn’t exist in the database, without using the IF EXISTS clause, as shown below −

DROP VIEW NEW;

An error will be generated −

ERROR 1051 (42S02): Unknown view  sample.NEW 

But if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the specified EVENT will be dropped and if a VIEW with the given name doesn’t exist the query will be ignored.

DROP VIEW IF EXISTS NEW;

The query will be ignored with the following output displayed −

(0 rows affected)

Deleting Rows from a View

Instead of removing an entire view we can also delete selected rows of a view using the DELETE statement.

Syntax

Following is the syntax of the DELETE statement −

DELETE FROM view_name WHERE condition;

Example

Following query deletes a record from the third_view created on the CUSTOMERS table created above. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETE FROM third_view WHERE age = 22;

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, try to query the base table and the SELECT statement would produce the following result. −

SELECT FROM * CUSTOMERS;

The CUSTOMERS table is displayed as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+