Can you update data through a View?

Difficulty: 5/10

updatable views, WITH CHECK OPTION, triggers

Updating Data Through a View in MySQL

Yes, you can update data through a view in MySQL, but only if the view is updatable. MySQL allows updates, inserts, and deletes through certain types of views, depending on their structure and rules.

When a View IS Updatable

1
The view is based on a single table.
2
The SELECT does not include aggregate functions (SUM, COUNT, AVG).
3
The SELECT does not use DISTINCT, GROUP BY, HAVING, UNION, or LIMIT.
4
The view includes all NOT NULL columns from the base table (unless columns have default values).
5
The view does not use subqueries in the SELECT list.

When a View Is NOT Updatable

1
The view contains JOINs.
2
The view uses aggregate functions or GROUP BY.
3
The view uses UNION or DISTINCT.
4
The view is based on multiple tables.
5
The view contains derived columns (e.g., price * quantity AS total).

Example of an Updatable View

The above UPDATE works because the view references a single table and contains no disqualifying features.

Example of a Non-Updatable View (JOIN)

Enforcing Update Rules

1
WITH CHECK OPTION ensures updates follow the view's WHERE clause.
2
Prevents updates that would make the row disappear from the view.

Example with CHECK OPTION

Scenario Questions

0-2 years experience

2-5 years experience

1Our application uses a view that joins orders and customers, and we need to allow updating order status via the view. What changes would you make to make the view updatable?

Follow-up Questions