UPDATE & DROP View
In this page:
Replacing a View
CREATE OR REPLACE VIEW lets you redefine an existing view's underlying query in one step, without first having to drop it and recreate it separately — useful when requirements change and the view needs a new definition.
Example: Replacing a View
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 500), (2, 30);
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100;
CREATE OR REPLACE VIEW big_orders AS SELECT * FROM orders WHERE total > 200;
SELECT * FROM big_orders;Altering View Structures
ALTER VIEW updates the query a view runs while keeping the view's name and any privileges granted on it intact, which avoids having to reassign permissions the way dropping and recreating would require.
Example: Altering View Structures
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 500);
CREATE VIEW order_view AS SELECT id FROM orders;
ALTER VIEW order_view AS SELECT id, total FROM orders;
SELECT * FROM order_view;Updating Data Through a View
If a view is built from a single underlying table with no aggregation or joins, you can actually run UPDATE statements against the view itself, and MySQL will apply the change to the real table behind it.
Example: Updating Data Through a View
CREATE TABLE customers (id INT, name TEXT);
INSERT INTO customers VALUES (1, 'Amit');
CREATE VIEW customer_view AS SELECT * FROM customers;
UPDATE customer_view SET name = 'Amit Kumar' WHERE id = 1;
SELECT * FROM customers;The WITH CHECK OPTION Clause
Adding WITH CHECK OPTION to a view's definition blocks any update that would produce a row no longer matching the view's own WHERE clause, preventing a row from silently disappearing from the view right after you edit it.
Example: The WITH CHECK OPTION Clause
CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 500);
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100 WITH CHECK OPTION;
-- Blocked: would drop the row below the view's own 100 threshold
-- UPDATE big_orders SET total = 50 WHERE id = 1;
SELECT * FROM big_orders;Removing Views with DROP VIEW
DROP VIEW permanently removes a view definition from the database. Since a view stores no data of its own, dropping it never touches the underlying tables — it simply deletes the saved query.
Example: Removing Views with DROP VIEW
CREATE TABLE orders (id INT, total INT);
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100;
DROP VIEW big_orders;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: