SQL - DROP or DELETE View

---

SQL allows you to drop an exting view and delete records from a view in a database. SQL uses DROP statement to delete all the records from the view along with its definition And using the DELETE statement, only the records are deleted while the view definition of the view remains unchanged.

And note that if a record is deleted from a view, it is also deleted from its corresponding base table.

The DROP VIEW Statement

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 drop indexed views with this statement.

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

• While trying to perform queries, the database engine checks all the objects referenced in that statement are valid 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 ),
   (2, 'Khilan', 25, 'Delhi', 1500.00 ),
   (3, 'kaushik', 23, 'Kota', 2000.00 ),
   (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
   (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
   (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
   (7, 'Muffy', 24, 'Indore', 10000.00 );

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

CREATE VIEW CUSTOMERS_VIEW1 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW2 AS SELECT * FROM CUSTOMERS;
CREATE VIEW CUSTOMERS_VIEW3 AS SELECT * FROM CUSTOMERS;

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

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

This will display the list of views as follows −

Now, lets drop two views from the above created views using the DROP VIEW statement.

DROP VIEW CUSTOMERS_VIEW1;
DROP VIEW CUSTOMERS_VIEW2;

Verification

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

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

The remaining list of views is as follows −

The IF EXISTS clause

While deleting an existing view, 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 DEMO_VIEW;

An error will be generated −

ERROR 1051 (42S02): Unknown table 'tutorials.demo_view'

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 DEMO_VIEW;

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.04 sec)

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 CUSTOMERS_VIEW3 WHERE AGE = 22;

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

SELECT * FROM CUSTOMERS;

The CUSTOMERS table is displayed as −