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

1We have a simple view that selects id and name from the users table. How would you write an UPDATE statement to change a user's name through that view?
2If you try to update a column that isn’t listed in the view definition, what error would you expect and why?

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?
2A teammate reports that updating through a view now fails with ‘View's SELECT statement contains a subquery’. How would you debug and fix this issue?

5-8 years experience

1We plan to expose a set of updatable views as a public API layer to hide schema changes. What are the performance and security trade‑offs, and how would you ensure data integrity at scale?
2If many services concurrently update rows through the same view, what concurrency problems could arise and how would you mitigate them using triggers, WITH CHECK OPTION, or transaction isolation levels?

8+ years experience

1Our legacy monolith stores business logic in updatable MySQL views. As we migrate to microservices, how would you decide whether to keep, refactor, or replace those views, considering deployment pipelines, data contracts, and team ownership?
2Design a migration strategy to replace updatable views with a service layer that enforces business rules, ensuring zero downtime and data consistency across multiple databases.

Follow-up Questions

What happens if the view includes an aggregate or GROUP BY?
How does WITH CHECK OPTION affect what rows can be inserted or updated?
Can you describe a situation where updating through a view would be a poor design choice?

Sharethis question

Share via WhatsApp, X, Facebook, LinkedIn or copy link. Open Graph preview enabled.