MySQL Drop View (original) (raw)

Last Updated : 23 Jul, 2025

**MySQL is a powerful **open-source relational database management system that is widely used for building scalable and high-performance databases. Developed by **MySQL AB, which is currently owned by Oracle Corporation, MySQL has been around since 1995.

It is known for its **robust, easy-to-use, and **reliable features, as well as its quick processing speeds. MySQL is particularly popular among dynamic web applications and is often used in conjunction with server-side programming languages like **PHP and **Python. In this article, you will learn about how to **DROP a **VIEW in **MySQL. You will learn how the **DROP **VIEW along with some examples.

MySQL DROP VIEW Statement

In relational database management systems (**RDBMS) like **MySQL, a **VIEW is a virtual table interactive with data generated from one or more underlying tables through either a defined query. Unlike a regular table, the **VIEW as a query doesn’t store the data itself. Instead, it creates a result set when someone queries it. For dropping a **VIEW inMYSQLthe view should be already existing.

Syntax:

DROP VIEW view_name;

• **DROP VIEW: This is the SQL keyword indicating that you want to drop (delete) a view from the database.
• **view_name: This is the name of the view you want to drop. In your case, **view_name should be replaced with the actual name of the view you wish to delete.

**Examples of MySQL DROP VIEW Statement

Let’s take an example of the **EMPLOYEE table having **EMP_ID, **NAME, **AGE, and **SALARY as columns.

CREATE TABLE EMPLOYEE (
EMP_ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
SALARY INT
);

Insert the data on it:

INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES
(1, 'Sahil', 21, 15000),
(2, 'Alen', 22, 13000),
(3, 'John', 22, 14000),
(4, 'Alex', 20, 13000),
(5, 'Mathew', 22, 14000),
(6, 'Sia', 21, 15000),
(7, 'David', 22, 16000),
(8, 'Tim', 21, 14000),
(9, 'Leo', 20, 15000),
(10, 'Tom', 21, 16000);

Output of the **EMPLOYEE**table:

Let's first CREATE 2 VIEWS from the **EMPLOYEE Table.

Query:

CREATE VIEW view1 AS
SELECT EMP_ID, SALARY
FROM EMPLOYEE

CREATE VIEW view2 AS
SELECT EMP_ID, AGE, SALARY
FROM EMPLOYEE
WHERE SALARY=14000;

**Output: view1

view1

**view2:

view2

Examples of MySQL drop view statement

Example 1: Drop view1 using the Drop View statement

Syntax:

DROP VIEW view_name;

Query:

DROP VIEW view1;

Output:

Dropped Successful

**Explanation: Here we are dropping a view1 using the **DROP VIEWstatement. The view1 had 10 rows present in it and after dropping it all 10 rows were deleted from the view and also the view got dropped.

Example 2: Drop view2 using the Drop View statement

Syntax:

DROP VIEW view_name;

Query:

DROP VIEW view2;

Output:

Dropped Successful

**Explanation: Here we are dropping a view2 using the **DROP VIEW statement. The view2 had 3 rows present in it and after dropping it all 3 rows were deleted from the view and also the view got dropped.

Using the IF EXISTS Clause

When attempting to drop a view that doesn’t exist, MySQL normally throws an error. To prevent this error and ensure smooth execution, you can employ the IF EXISTS clause. If the view exists, it is dropped; if it doesn't, the statement is silently ignored.

Example

Suppose we want to drop a view named 'NEW_1'. Without IF EXISTS, MySQL would produce an error if 'NEW_1' doesn't exist:

DROP VIEW NEW_1;

**Output:

ERROR 1051 (42S02)

To handle this scenario gracefully and avoid errors, use IF EXISTS:

DROP VIEW IF EXISTS NEW_1;

*Output: If '*NEW_1**' exists, it is dropped; otherwise, no action is taken, and no error is reported.

Deleting Rows from a View

In MySQL, you can delete specific rows from a view using the DELETE statement with a WHERE clause. This operation affects the underlying base table from which the view is created, reflecting the changes accordingly.

**Syntax

The syntax for deleting rows from a view is similar to deleting rows from a table:

DELETE FROM view_name WHERE condition;

• **DELETE FROM: This SQL clause indicates that you want to delete rows from a table or view.
• **view_name: Replace this with the name of the view from which you want to delete rows.
• **WHERE condition: Optional clause that specifies the conditions for rows to be deleted.

Example

Let's explain this with an example using a view called testView created on the **EMPLOYEE**table. Suppose testView contains certain records based on a predefined query:

CREATE VIEW testView AS
SELECT * FROM EMPLOYEE
WHERE SALARY > 14000;

Now, to delete rows from testView where the AGE is greater than 21:

DELETE FROM testView WHERE AGE > 21;

After performing the deletion operation, the resulting data in 'testView'(and indirectly in the EMPLOYEE table) will be:

*Explanation: This statement deletes all records from '*testView**' where the **AGE**of employees is greater than 21. The changes made to testView will be reflected in the base table EMPLOYEE.

Verification

After executing the DELETE statement, you can verify the updated records in the underlying table EMPLOYEE:

SELECT * FROM EMPLOYEE;

After performing the deletion operation, the resulting data in testView(and indirectly in the EMPLOYEE table) will be:

**Output: This query will display the current records in the **EMPLOYEE**table, reflecting the changes made through the **DELETE**operation on the testView.

Conclusion

In Conclusion, the **DROP **VIEW statement inMySQLdoes not only provide an easier and more effective way for removing **VIEWS from the database schematic; it also ensures **lower maintenance. Through this sentence, users will be able to set up and control their **database structure with ease which is a critical aspect of having organised and efficient database management. When it comes to running misleading queries or changing the data layout, **DROP **VIEW allows users to structure their databases by offering accuracy and simplicity.