Updatable views in MySQL - Step-by-Step Execution

Concept Flow - Updatable views

Create View

↓

Query View

↓

Update View Data?

↓

Update Base Table

↓

Reflect Changes in View

This flow shows how an update on a view is checked and either applied to the base table or rejected if not allowed.

Execution Sample

MySQL

CREATE VIEW v_emp AS SELECT id, name, salary FROM employees;
UPDATE v_emp SET salary = salary + 100 WHERE id = 2;

This code creates a simple view and updates the salary of employee with id 2 through the view.

Execution Table

Step	Action	Evaluation	Result
1	Create view v_emp	View created on employees(id, name, salary)	View v_emp ready
2	Update v_emp set salary = salary + 100 where id = 2	Check if view is updatable	View is updatable
3	Translate update to base table	Update employees set salary = salary + 100 where id = 2	Base table updated
4	Reflect changes in view	Query view for id=2	Salary increased by 100
5	End	No more updates	Update complete

💡 Update stops after applying changes to base table and reflecting in view

Variable Tracker

Variable	Start	After Step 3	Final
salary (id=2)	5000	5100	5100

Key Moments - 2 Insights

Why can we update the view but not all views are updatable?

Only views that directly map to a single base table without complex joins or aggregations are updatable, as shown in step 2 where the system checks if the view is updatable.

What happens if the view is not updatable?

The update is rejected and does not affect the base table, unlike step 3 where the update is translated and applied.

Visual Quiz - 3 Questions

Look at the execution table, what is the salary of employee with id=2 after step 3?

A 5000

B 5100

C 5200

D 4900

💡 Hint

Check the variable_tracker row for salary after step 3

At which step does the system confirm the view can be updated?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the evaluation column in the execution_table for step 2

If the view included a join, what would happen at step 2?

A Update partially applied

B Update proceeds normally

C Update is rejected

D View is dropped

💡 Hint

Refer to key_moments about updatable views and step 2 evaluation

Concept Snapshot

CREATE VIEW view_name AS SELECT columns FROM table;
Updates on simple views affect the base table directly.
Only views without joins, aggregates, or complex expressions are updatable.
Updating a view translates to updating the underlying table.
If not updatable, update commands on the view fail.

Full Transcript

Updatable views in MySQL allow you to change data through a view if the view is simple enough. The process starts by creating a view that selects columns from a base table. When you try to update the view, MySQL checks if the view is updatable. If yes, it translates the update to the base table and applies it. The changes then show up in the view. If the view is complex, like having joins or aggregates, the update is rejected. This ensures data integrity and clear mapping between views and tables.