Updatable Views

What Makes a View Updatable?

An updatable view is one through which you can run INSERT, UPDATE, or DELETE, and have the change reflected in the underlying base table — as if you had written directly against the table.

sql
CREATE VIEW high_earners AS
SELECT id, name, salary, department
FROM employees
WHERE salary > 80000;

UPDATE high_earners
SET salary = salary * 1.05
WHERE id = 101;
-- This actually updates the employees table's row where id = 101

sql
INSERT INTO high_earners (id, name, salary, department)
VALUES (250, 'Priya Rao', 95000, 'Engineering');
-- Inserts a new row directly into employees

The Requirements

A view is generally updatable only when the SQL standard's (and each engine's) conditions hold, most importantly:

1. Single base table — no joins.
2. No aggregate functions (SUM, COUNT, AVG, etc.).
3. No `GROUP BY` or `HAVING`.
4. No `DISTINCT`.
5. No set operations (UNION, INTERSECT, EXCEPT).
6. Every NOT NULL column without a default in the base table must be included in the view's column list (for INSERT to succeed).

high_earners satisfies all of these: it's a straight SELECT from one table with a simple WHERE filter, so each view row maps to exactly one employees row.

Why Complex Views Usually Can't Be Updated

Consider department_summary from 19.2:

sql
CREATE VIEW department_summary AS
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary
FROM departments d LEFT JOIN employees e ON e.department = d.id
GROUP BY d.name;

sql
UPDATE department_summary SET avg_salary = 75000 WHERE name = 'Sales';
-- ERROR: cannot update a view with aggregate functions

This fails for a fundamental reason, not a syntactic one: "the Sales department's average salary" is not a single stored value — it's derived from potentially dozens of employee rows. Setting it to 75000 doesn't tell the database which employees' salaries to change, or by how much each one should move, to make the average come out to 75000. The mapping from one view row to underlying rows is one-to-many and ambiguous, so there's no well-defined way to push the update down. The same logic applies to joins: updating a joined view row might logically need to touch two different tables at once, and the engine has no reliable way to know how to split the change (unless it's a WITH CHECK OPTION-style single-table-affecting join, which some engines like PostgreSQL support with restrictions via INSTEAD OF triggers).

WITH CHECK OPTION

sql
CREATE VIEW high_earners AS
SELECT id, name, salary, department
FROM employees
WHERE salary > 80000
WITH CHECK OPTION;

This prevents INSERT/UPDATE operations through the view from creating a row that wouldn't satisfy the view's own WHERE clause — e.g., you can no longer UPDATE high_earners SET salary = 50000 and silently push a row out of the view's visibility; the statement is rejected instead.

INSTEAD OF Triggers: Making Complex Views "Updatable" Anyway

Some engines let you attach an INSTEAD OF trigger to a non-updatable view, letting you write custom procedural logic that decides how an update should be distributed across underlying tables:

sql
CREATE TRIGGER dept_summary_update
INSTEAD OF UPDATE ON department_summary
FOR EACH ROW
BEGIN
    -- custom logic to decide what an "average salary" update even means
END;

This doesn't make the view natively updatable — it substitutes your own procedural interpretation for the ambiguous case.

Edge Cases

• A simple view with a WHERE clause referencing a column not included in the view's own SELECT list can still be updatable in some engines but not others — behavior varies.
• A view over a single table that includes a computed/expression column (e.g., salary * 1.1 AS projected_salary) is typically NOT updatable through that specific column, since there's no direct underlying column to write to.
• MySQL, PostgreSQL, and Oracle all differ slightly in exactly which single-table view shapes they consider updatable — always check engine documentation for edge conditions.

Key Takeaways / Interview Q&A

Q: Is every simple (single-table) view automatically updatable? A: Not necessarily — additional conditions apply (no aggregation, GROUP BY, DISTINCT, and required NOT NULL columns present), but most straightforward single-table filtered views are updatable.

Q: Why can't you UPDATE a view containing AVG(salary)? A: Because the aggregate value doesn't correspond to any single underlying row — there's no unambiguous way to translate "set the average to X" into concrete row-level changes.

Q: What does WITH CHECK OPTION protect against? A: It prevents INSERT/UPDATE through a view from producing a row that the view's own WHERE clause would then exclude.