Updatable views and limitations in SQL - Step-by-Step Execution

Concept Flow - Updatable views and limitations

Create View

↓

Query View

↓

Check if View is Updatable

↓

Reflect Changes in View

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

Execution Sample

SQL

CREATE VIEW v_emp AS
SELECT emp_id, emp_name FROM employees;

UPDATE v_emp SET emp_name = 'Anna' WHERE emp_id = 2;

This code creates a simple view and updates a column through the view if it is updatable.

Execution Table

Step	Action	Check/Condition	Result	Effect
1	Create view v_emp	View definition simple select from one table	View created	View ready for queries
2	Update v_emp set emp_name='Anna' where emp_id=2	Is view updatable? (simple select, no joins, no aggregates)	Yes	Proceed to update base table
3	Apply update to employees table	Row with emp_id=2 exists?	Yes	emp_name changed to 'Anna' in employees
4	Reflect update in view	View reads updated base table	Updated row visible in view	View shows emp_name='Anna' for emp_id=2
5	Try update on non-updatable view	View has join or aggregate	No	Update rejected with error

💡 Update stops if view is not updatable or after base table is updated successfully

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
emp_name for emp_id=2	'John'	Attempt to update to 'Anna'	'Anna'	'Anna'	'Anna'

Key Moments - 3 Insights

Why can't we update a view that has a join?

Because the view combines multiple tables, the database cannot determine which base table to update, so the update is rejected as shown in execution_table row 5.

What makes a view updatable?

A view is updatable if it is based on a single table without aggregates, group by, or distinct, as checked in execution_table row 2.

Does updating a view always change the data?

No, only if the view is updatable and the base table row exists, otherwise the update is rejected or has no effect (execution_table rows 3 and 5).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database decide if the view is updatable?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Check the 'Check/Condition' column in execution_table row 2 for the updatability check.

According to variable_tracker, what is the value of emp_name for emp_id=2 after step 3?

A 'Anna'

B 'Anna' updated in view only

C 'John'

D No change

💡 Hint

Look at variable_tracker row for emp_name after Step 3.

If the view included a join, what would happen when trying to update it?

A Update succeeds and changes both tables

B Update is rejected with error

C Update succeeds but changes only one table

D Update silently ignored

💡 Hint

See execution_table row 5 where update on non-updatable view is rejected.

Concept Snapshot

Updatable views allow changes through the view to affect base tables.
Views are updatable if based on a single table without joins, aggregates, or distinct.
Updates on non-updatable views cause errors.
Check view definition before updating.
Changes reflect in base tables and then in the view.

Full Transcript

This visual execution trace shows how SQL handles updates on views. First, a view is created from a single table. When an update is issued on the view, the database checks if the view is updatable, meaning it is a simple select from one table without joins or aggregates. If yes, the update is applied to the base table row matching the condition. The change then appears in the view. If the view is not updatable, such as when it includes joins, the update is rejected with an error. Variables like the employee name change step-by-step as the update proceeds. Key moments clarify why joins prevent updates and what makes a view updatable. Quiz questions test understanding of these steps and outcomes.