Updatable Views in MySQL

A view in MySQL is considered updatable if certain conditions are met.
Updatable views allow you to perform INSERT, UPDATE, or DELETE operations on the view, and the changes will reflect in the underlying base table.

1. Conditions for an Updatable View

• It references a single base table.
• It does not contain aggregate functions (SUM(), AVG(), etc.), DISTINCT, GROUP BY, HAVING, UNION, or subqueries in the SELECT list.
• It does not include LIMIT or OFFSET.

2. Deleting from an Updatable View

If a view is updatable, you can delete rows from it, which will also delete the corresponding rows from the base table.

Syntax:

DELETE FROM view_name
WHERE condition;

Example:

DELETE FROM active_customers
WHERE id = 5;

3. WITH CHECK OPTION

The WITH CHECK OPTION ensures that all updates or inserts through the view satisfy the view’s defining conditions.
Any operation that violates the view’s conditions will be rejected.

Syntax:

CREATE VIEW view_name AS
SELECT ... FROM ... WHERE condition
WITH CHECK OPTION;

Example:

CREATE VIEW active_users AS
SELECT * FROM users
WHERE status = 'active'
WITH CHECK OPTION;

Updates that attempt to change the status to anything other than ‘active’ will be rejected when done through this view.