SQLite DROP VIEW

Summary: in this tutorial, you will learn how to use the SQLite DROP VIEW statement to remove a view from its database schema.

Introduction to SQLite DROP VIEW statement

The DROP VIEW statement deletes a view from the database schema. Here is the basic syntax of the DROP VIEW statement:

DROP VIEW [IF EXISTS] [schema_name.]view_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view that you wants to remove after the DROP VIEW keywords.
• Second, specify the schema of the view that you want to delete.
• Third, use the IF EXISTS option to remove a view only if it exists. If the view does not exist, the DROP VIEW IF EXISTS statement does nothing. However, trying to drop a non-existing view without the IF EXISTS option will result in an error.

Note that the DROP VIEW statement only removes the view object from the database schema. It does not remove the data of the base tables.

SQLite DROP VIEW statement examples

This statement creates a view that summarizes data from the invoices and invoice_items in the sample database:

CREATE VIEW v_billings (
    invoiceid,
    invoicedate,
    total
)
AS
    SELECT invoiceid,
           invoicedate,
           sum(unit_price * quantity) 
      FROM invoices
           INNER JOIN
           invoice_items USING (
               invoice_id
           );
Code language: SQL (Structured Query Language) (sql)

To delete the v_billings view, you use the following DROP VIEW statement:

DROP VIEW v_billings;
Code language: SQL (Structured Query Language) (sql)

This example uses the IF EXISTS option to delete a non-existing view:

DROP VIEW IF EXISTS v_xyz;
Code language: SQL (Structured Query Language) (sql)

It does not return any error. However, if you don’t use the IF EXISTS option like the following example, you will get an error:

DROP VIEW v_xyz;
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

Error while executing SQL query on database 'chinook': no such view: v_xyz

In this tutorial, you have learned how to use the SQLite DROP VIEW statement to remove a view from its database schema.