DROP VIEW

This statement removes a view from the database.

See also CREATE VIEW.

Views that depend on the dropped view are not automatically deleted by the database but are marked as INVALID. These invalid views can either be deleted, redefined, or made valid again by redefining other views on which they depend.

Syntax

DROP VIEW folder_name.view_name;

Parameters

folder_name: The name of your folder.

---

view_name: The name of the view.

Example

Create tables:

CREATE TABLE mkw_doku.prod (id_prod INTEGER, prod_cat_id INTEGER, prod STRING);
CREATE TABLE mkw_doku.prod_cat (id_cat INTEGER, prod_cat STRING);
INSERT INTO mkw_doku.prod VALUES(1,1,'Blue Jeans'); 
INSERT INTO mkw_doku.prod VALUES(2,1,'Red Jeans'); 
INSERT INTO mkw_doku.prod VALUES(3,2,'Yellow Hat'); 
INSERT INTO mkw_doku.prod VALUES(4,2,'Red Hat');
INSERT INTO mkw_doku.prod_cat VALUES(1,'Jeans');
INSERT INTO mkw_doku.prod_cat VALUES(2,'Hat');
 
SELECT * FROM mkw_doku.prod;

+---------+-------------+------------+
| ID_PROD | PROD_CAT_ID | PROD       |
+---------+-------------+------------+
| 1       | 1           | Blue Jeans |
| 2       | 1           | Red Jeans  |
| 3       | 2           | Yellow Hat |
| 4       | 2           | Red Hat    |
+---------+-------------+------------+

SELECT * FROM mkw_doku.prod_cat;

+--------+----------+
| ID_CAT | PROD_CAT |
+--------+----------+
| 1      | Jeans    |
| 2      | Hat      |
+--------+----------+

Create a view:

CREATE OR REPLACE VIEW mkw_doku.my_view AS 
    SELECT pd.*, pdc.* FROM mkw_doku.prod pd, mkw_doku.prod_cat pdc 
                       WHERE pd.prod_cat_id=pdc.id_cat;
SELECT * FROM mkw_doku.my_view;

+---------+-------------+------------+--------+----------+
| ID_PROD | PROD_CAT_ID | PROD       | ID_CAT | PROD_CAT |
+---------+-------------+------------+--------+----------+
| 1       | 1           | Blue Jeans | 1      | Jeans    |
| 2       | 1           | Red Jeans  | 1      | Jeans    |
| 4       | 2           | Red Hat    | 2      | Hat      |
| 3       | 2           | Yellow Hat | 2      | Hat      |
+---------+-------------+------------+--------+----------+

Create a view that depends on another view:

CREATE OR REPLACE VIEW mkw_doku.my_view_2 AS 
    SELECT * FROM mkw_doku.my_view WHERE id_cat = 2; 
SELECT * FROM mkw_doku.my_view_2;

+---------+-------------+------------+--------+----------+
| ID_PROD | PROD_CAT_ID | PROD       | ID_CAT | PROD_CAT |
+---------+-------------+------------+--------+----------+
| 4       | 2           | Red Hat    | 2      | Hat      |
| 3       | 2           | Yellow Hat | 2      | Hat      |
+---------+-------------+------------+--------+----------+

Delete the parent view

DROP VIEW mkw_doku.my_view;

SELECT * FROM mkw_doku.my_view_2;

The dependent view is not removed, but queries from the dependent view will fail:

CompileError: Table or View 'mkw_doku.my_view' does not exist!
 at mkw_doku.my_view_2 (1:1)
 at mkw_doku.test_abfragen (3:1)

Recreate the parent view

CREATE OR REPLACE VIEW mkw_doku.my_view AS 
    SELECT pd.*, pdc.* FROM mkw_doku.prod pd, mkw_doku.prod_cat pdc 
                       WHERE pd.prod_cat_id=pdc.id_cat;

SELECT * FROM mkw_doku.my_view_2;

The query from the dependent view works again:

+---------+-------------+------------+--------+----------+
| ID_PROD | PROD_CAT_ID | PROD       | ID_CAT | PROD_CAT |
+---------+-------------+------------+--------+----------+
| 4       | 2           | Red Hat    | 2      | Hat      |
| 3       | 2           | Yellow Hat | 2      | Hat      |
+---------+-------------+------------+--------+----------+