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.

Practice

1. Which of the following best describes an updatable view in SQL?

easy

A. A view created using multiple tables joined together.

B. A view that only shows data but does not allow any changes.

C. A view that contains aggregate functions like SUM or COUNT.

D. A view that allows you to insert, update, or delete rows through it.

Solution

1. Step 1: Understand what an updatable view is

   An updatable view lets you change data (insert, update, delete) through the view as if it were a table.

2. Step 2: Compare options to definition

   Options B, C, and D describe views that are either read-only or complex and usually not updatable.

3. Final Answer:

   A view that allows you to insert, update, or delete rows through it. -> Option D

4. Quick Check:

   Updatable view = allows data changes [OK]

Hint: Updatable views let you change data through them [OK]

Common Mistakes:

• Thinking all views allow data changes
• Confusing aggregate views as updatable
• Assuming joins always allow updates

2. Which SQL syntax correctly creates a simple updatable view on a single table employees showing id and name?

easy

A. CREATE VIEW emp_view AS SELECT id, name FROM employees GROUP BY id;

B. CREATE VIEW emp_view AS SELECT id, name, COUNT(*) FROM employees;

C. CREATE VIEW emp_view AS SELECT id, name FROM employees;

D. CREATE VIEW emp_view AS SELECT id, name FROM employees JOIN departments ON employees.dept_id = departments.id;

Solution

1. Step 1: Identify simple view syntax

   CREATE VIEW emp_view AS SELECT id, name FROM employees; selects columns directly from one table without grouping or joins, which is valid for an updatable view.

2. Step 2: Check other options for complexity

   Options A and C use GROUP BY or aggregate functions, making views non-updatable. CREATE VIEW emp_view AS SELECT id, name FROM employees JOIN departments ON employees.dept_id = departments.id; uses JOIN, also usually non-updatable.

3. Final Answer:

   CREATE VIEW emp_view AS SELECT id, name FROM employees; -> Option C

4. Quick Check:

   Simple select from one table = updatable view syntax [OK]

Hint: Simple SELECT without joins or aggregates creates updatable views [OK]

Common Mistakes:

• Adding GROUP BY or aggregates in view
• Using JOINs in view definition
• Forgetting to select from only one table

3. Given the view definition:

CREATE VIEW dept_salary AS SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

Which of the following statements is true when trying to update avg_salary through this view?

medium

A. You can update avg_salary directly and it will change salaries in employees.

B. You cannot update avg_salary because the view uses aggregation.

C. Updating avg_salary will update all salaries in the department equally.

D. The view allows updates only if you use INSTEAD OF triggers.

Solution

1. Step 1: Analyze the view's use of aggregation

   The view uses AVG() and GROUP BY, which creates a summary, not individual rows.

2. Step 2: Understand update limitations on aggregated views

   Aggregated columns like avg_salary cannot be updated directly because they do not map to single rows.

3. Final Answer:

   You cannot update avg_salary because the view uses aggregation. -> Option B

4. Quick Check:

   Aggregated views are not updatable by default [OK]

Hint: Aggregated views block direct updates [OK]

Common Mistakes:

• Trying to update aggregate columns
• Assuming updates affect underlying rows automatically
• Ignoring need for triggers on complex views

4. You have a view defined as:

CREATE VIEW emp_dept AS SELECT e.id, e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

Attempting to update dept_name through this view causes an error. What is the main reason?

medium

A. Views with JOINs are not updatable by default.

B. You cannot update columns with aliases in views.

C. The view lacks a WHERE clause to filter rows.

D. The view must include all columns from both tables to be updatable.

Solution

1. Step 1: Identify the view's structure

   The view uses a JOIN between employees and departments tables.

2. Step 2: Recall update limitations on joined views

   Views involving JOINs are generally not updatable because the database cannot determine which table to update.

3. Final Answer:

   Views with JOINs are not updatable by default. -> Option A

4. Quick Check:

   JOIN views block updates unless special rules apply [OK]

Hint: JOINs in views block updates by default [OK]

Common Mistakes:

• Thinking alias columns block updates
• Believing WHERE clause affects updatability
• Assuming all columns must be selected

5. You want to create an updatable view that allows changes to employee names and salaries but also shows department names. Which approach is best?

hard

A. Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates.

B. Create a simple view on employees only and join departments in queries outside the view.

C. Create a view with GROUP BY department to summarize salaries and allow updates.

D. Create a view with aggregate functions and update the aggregates directly.

Solution

1. Step 1: Understand the requirement

   You want to update employee data but also show department names, which requires joining tables.

2. Step 2: Recognize limitations of joined views

   Joined views are not updatable by default, but INSTEAD OF triggers can enable updates by defining custom behavior.

3. Step 3: Evaluate other options

   Create a simple view on employees only and join departments in queries outside the view. avoids joins in the view but does not show department names in the view itself. Options C and D use aggregates, which block updates.

4. Final Answer:

   Create a view joining employees and departments, then use INSTEAD OF triggers to handle updates. -> Option A

5. Quick Check:

   Use triggers to update joined views [OK]

Hint: Use INSTEAD OF triggers to update joined views [OK]

Common Mistakes:

• Trying to update aggregates directly
• Ignoring triggers for complex views
• Using simple views that lack needed columns