? MySQL Updatable Views

? Quick Overview

An Updatable View in MySQL is a view that allows INSERT, UPDATE, and DELETE operations. Any change made through the view directly affects the underlying base table.

? Key Concepts

A view is a virtual table based on a SELECT query
Not all views are updatable
Updatable views map directly to base table rows
Changes through views affect original tables

? Syntax / Theory

A view becomes updatable only when MySQL can clearly identify which base table row should be modified. This depends on the structure of the SELECT query used to create the view.

? Code Example(s)

? View Code Example

-- Creating a simple updatable view
CREATE VIEW active_customers AS
SELECT customer_id, full_name, city
FROM customers
WHERE city = 'Pune';

? View Code Example

-- Updating data using an updatable view
UPDATE active_customers
SET city = 'Mumbai'
WHERE customer_id = 3;

? Live Output / Explanation

What Happens Internally?

When the UPDATE query runs on the view:

MySQL converts it into an UPDATE on the base table
The row in customers with customer_id = 3 is updated
The view reflects the new data instantly

✅ Conditions for Updatable Views

No JOIN clauses
No aggregate functions (SUM, COUNT, etc.)
No GROUP BY or DISTINCT
Single base table only
No subqueries in SELECT list

? Tips & Best Practices

Use updatable views to restrict column-level access
Apply WHERE conditions to control row-level updates
Always test INSERT and UPDATE on views in development
Use views to improve security and readability

? Try It Yourself

Create a view showing only active orders
Update order status using the view
Try inserting a new row via the view
Test what happens if conditions are violated