SQL - Drop Views

SQL - Dropping and Deleting from Views

In SQL, it’s important to distinguish between removing a view’s definition entirely and removing data through a view. The DROP VIEW statement deletes the view object itself, while the DELETE statement removes rows from the view’s underlying base table.

Key Distinction: DROP VIEW removes the saved query object. DELETE FROM my_view removes rows from the table the view is based on. The view’s definition remains.

The DROP VIEW Statement

The DROP VIEW statement is used to completely remove an existing view from the database. When a view is dropped, its definition and all associated permissions are permanently deleted.

To drop a view, you generally need appropriate permissions on that view, such as DROP or ALTER.

Syntax

The basic syntax is straightforward:

DROP VIEW view_name;

Example

First, let’s set up a base table and a few views.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Smith', 'Engineering', 90000),
(2, 'Bob', 'Johnson', 'Engineering', 85000),
(3, 'Charlie', 'Brown', 'HR', 65000);

-- Create some views
CREATE VIEW v_engineers AS SELECT * FROM employees WHERE department = 'Engineering';
CREATE VIEW v_hr_staff AS SELECT * FROM employees WHERE department = 'HR';
CREATE VIEW v_all_staff AS SELECT employee_id, first_name, last_name FROM employees;

Now, let’s drop the v_hr_staff view.

DROP VIEW v_hr_staff;

After this command, any attempt to query v_hr_staff will result in an error because the view no longer exists. The underlying employees table remains unaffected.

Using IF EXISTS for Safe Dropping

If you try to drop a view that doesn’t exist, the database will return an error. This can be problematic in automated scripts. The IF EXISTS clause, supported by most modern RDBMS, elegantly solves this by only attempting to drop the view if it’s present.

Syntax

DROP VIEW IF EXISTS view_name;

Example

-- This will fail with an error because the view does not exist.
DROP VIEW v_non_existent_view;
-- ERROR:  view "v_non_existent_view" does not exist

-- This will execute successfully (with a notice/warning) and do nothing.
DROP VIEW IF EXISTS v_non_existent_view;
-- Query OK, 0 rows affected, 1 warning

Deleting Rows from a View

You can use the DELETE statement on a view to remove rows from its underlying base table. However, this is only possible if the view is considered “updatable”.

Syntax

DELETE FROM view_name WHERE condition;

Example: Deleting through an Updatable View

The v_engineers view we created earlier is updatable. Let’s delete an employee through it.

-- This statement deletes the row for Bob Johnson from the underlying `employees` table.
DELETE FROM v_engineers WHERE employee_id = 2;

-- Verify the change in the base table
SELECT * FROM employees;

After the DELETE command, the employees table will no longer contain the record for Bob Johnson.

Understanding Updatable and Non-Updatable Views

A view is not always updatable (or deletable). A view is generally considered updatable only if the database can unambiguously determine which row in the base table needs to be modified. The rules are complex and can vary slightly between database systems, but generally, a view is NOT updatable if it contains:

• Joins (more than one table in the FROM clause)
• Aggregate functions (SUM(), COUNT(), AVG(), etc.)
• Window functions (ROW_NUMBER(), RANK(), etc.)
• GROUP BY or HAVING clauses
• DISTINCT
• Set operators like UNION, INTERSECT, or EXCEPT
• Calculated columns (e.g., SELECT salary * 1.1 AS increased_salary)

Example: Non-Updatable View

Let’s create a view that calculates the average salary per department. This view is not updatable.

CREATE VIEW v_avg_salary_by_dept AS
SELECT department, AVG(salary) as average_salary
FROM employees
GROUP BY department;

-- This command will FAIL because the view is not updatable.
DELETE FROM v_avg_salary_by_dept WHERE department = 'Engineering';
-- ERROR: cannot delete from view "v_avg_salary_by_dept"