Views — creating, updatable views: Worked examples

Before we start, three words we lean on constantly:

See SQL SELECT and JOINs for the SELECT mechanics, Materialized Views for the stored-result cousin, Triggers for the INSTEAD OF escape hatch, and the parent topic.

The scenario matrix

Every view you will ever meet falls into one of these case classes. The examples below hit every cell.

Cell	Case class	Updatable?	Covered by
A	Simple projection + `WHERE`, plain columns	✅ Yes	Ex 1
B	Insert that lands outside the `WHERE` (degenerate visibility)	⚠️ Succeeds but vanishes	Ex 2
B′	Same, guarded with `WITH CHECK OPTION`	✅ Rejected safely	Ex 2
C	Computed column (`price*1.1`) in SELECT list	❌ That column read-only	Ex 3
D	Aggregate + `GROUP BY` (many→one mapping)	❌ No	Ex 4
E	`DISTINCT` (collapses duplicates)	❌ No	Ex 5
F	Multi-table `JOIN`, key-preserved side	⚠️ Engine-dependent yes	Ex 6
F′	Same `JOIN`, touching the non-key-preserved side	❌ No	Ex 6
G	`UNION` (set operation)	❌ No	Ex 7
H	Non-updatable view rescued by `INSTEAD OF` trigger	✅ You supply the map	Ex 8
I	Real-world word problem (row-level security)	✅ Yes	Ex 9
J	Exam twist: `DELETE` vs `NULL` on a `WHERE` boundary	✅ Yes, subtle	Ex 10

The single picture behind all of this — when the arrow from view to base is one-to-one, you can write; when it fans out or funnels in, you cannot:

Figure — Views — creating, updatable views

Figure 1 — Left: a writable view where one view row pairs with one base row via a two-way (bijective) arrow. Right: a non-writable AVG view where three base rows fan up into one view row, so the reverse arrow has no single target.

Intuition Reading the diagram in words

On the left of the figure, one white "view row" box sits above one cyan "base row" box, with two amber arrows running between them — one down, one up. That double arrow is the bijection: from the view row you can point at exactly one base row, and from that base row back to exactly one view row. Because the reverse arrow lands on a single target, "set this column" has one and only one meaning — writable. On the right, one white view row labelled "AVG" sits above three cyan base rows (r1, r2, r3), and three cyan arrows fan up from the base rows into the single view row. Try to walk the arrow backwards — from the one view row down to the base — and it splits three ways. There is no single base row to change, so "set the average to 60000" has no unique meaning — not writable. Every non-updatable case in this page is, at heart, this right-hand fan-in (or a fan-out, as with UNION and joins).

Ex 1 — Cell A: the clean updatable view

Worked example Statement

CREATE VIEW cheap_items AS
SELECT item_id, name, price
FROM items
WHERE price < 100;

Base row: (7, 'Pen', 40). Run UPDATE cheap_items SET price = 90 WHERE item_id = 7; What is items.price for row 7 afterwards, and does the row stay visible in the view?

Forecast: guess the new price and whether the row survives before reading on.

Identify the row mapping. The view is a projection (item_id, name, price) plus a filter (price < 100). No aggregation, no join, no DISTINCT. Why this step? These are exactly the ingredients that keep the map one-to-one — cell A of the matrix.
Rewrite the write onto the base table. item_id = 7 picks one view row, which is one base row. So the engine rewrites to UPDATE items SET price = 90 WHERE item_id = 7;. Why this step? price is a plain base column, so "set price to 90" has an unambiguous meaning downstairs.
Check the new value against the view filter. New price is $90$ , and $90 < 100$ is true. Why this step? We must know whether the row stays in the view — an update can silently push a row out.

Ex 2 — Cells B & B′: the insert that disappears (and the guard)

Worked example Statement

Same cheap_items view. Run:

INSERT INTO cheap_items (item_id, name, price) VALUES (50, 'Mug', 150);

Does the insert succeed? Can you then see row 50 in the view? What changes if the view had WITH CHECK OPTION?

Forecast: succeed-and-visible / succeed-and-invisible / rejected — pick one.

Route the insert to the base table. An INSERT through a view is a base-table insert. The engine rewrites it to INSERT INTO items (item_id, name, price) VALUES (50, 'Mug', 150);. Why this step? The view holds no data; every write lands directly in items, and showing the rewritten SQL makes the target explicit.
Check validity against the base table only. items has columns item_id, name, price — the row is a legal items row, so it is stored. Why this step? The WHERE price < 100 filters reads, not writes — inserts don't consult it (cell B).
Now re-read the view. The view recomputes WHERE price < 100. Row 50 has price $150$ , and $150 < 100$ is false, so the row is absent from the view. Why this step? This is the "vanishing row" trap: your insert worked yet you cannot see it.
Add the guard (cell B′). Re-create with ... WHERE price < 100 WITH CHECK OPTION;. Now the engine tests the incoming row against the filter before writing. $150 < 100$ is false → the insert is rejected. Why this step? WITH CHECK OPTION makes the write-filter equal the read-filter, closing the trap.

Ex 3 — Cell C: the computed column is read-only

Worked example Statement

CREATE VIEW priced_up AS
SELECT item_id, name, price * 1.1 AS list_price
FROM items;

Try UPDATE priced_up SET list_price = 110 WHERE item_id = 7; (base price of 7 is 40). Does this work? Why or why not?

Forecast: allowed / rejected — and what would price even become?

Classify each SELECT column. item_id, name are plain base columns → updatable. list_price is price * 1.1, an expression → not a plain base column. Why this step? Only plain-column outputs have an obvious inverse write.
Try to invert the expression. To satisfy list_price = 110 you would need price * 1.1 = 110, i.e. $p r i ce = 110/1.1 = 100$ . The engine refuses to guess this inverse. Why this step? In general the expression may not be invertible (e.g. price*0), so SQL declares any expression column non-updatable rather than special-casing multiplication.
Consequence. UPDATE ... SET list_price = ... is rejected. You can still update name through this view, because that column is plain.

Ex 4 — Cell D: aggregate + GROUP BY, the many-to-one killer

Worked example Statement

CREATE VIEW dept_stats AS
SELECT dept_id, AVG(salary) AS avg_pay
FROM employees
GROUP BY dept_id;

Dept 3 has employees earning 50000, 60000, 70000. Run UPDATE dept_stats SET avg_pay = 60000 WHERE dept_id = 3; What is the current avg_pay, and why is the update impossible?

Forecast: compute the current average first, then decide if the update has a unique meaning.

Compute the view row. $a v g_p a y = (50000 + 60000 + 70000) /3 = 180000/3 = 60000$ . Why this step? To see what one view row summarises — three base rows funnel into one.
Look at the mapping. One view row (dept 3) is built from three base rows. This is many→one, not one↔one (cell D). Why this step? Writability needs the reverse arrow to point at a single base row.
Ask the reverse question. "Set avg to 60000" — but which of the three salaries do we change, and to what? Change one by any amount and offset another, and the average can still be anything. Infinitely many solutions. Why this step? No unique inverse ⇒ SQL rejects the update.

Ex 5 — Cell E: DISTINCT collapses rows

Worked example Statement

CREATE VIEW used_depts AS
SELECT DISTINCT dept_id FROM employees;

Dept 3 appears in employees for emp_ids 11, 12, 13. Run UPDATE used_depts SET dept_id = 9 WHERE dept_id = 3; Is this updatable?

Forecast: how many base rows sit behind the single dept_id = 3 view row?

Count the collapse. DISTINCT folds three base rows (emps 11, 12, 13, all dept 3) into one view row. Why this step? DISTINCT is a many→one collapse, just like GROUP BY (cell E).
Try the reverse write. Changing the single view row's dept_id from 3 to 9 — should it change all three employees, or just one? The mapping is ambiguous. Why this step? No unique base row to touch ⇒ non-updatable.
Reject. The engine refuses the update. Why this step? Because DISTINCT merged three distinct base rows into one view row, the reverse arrow fans out to three targets — there is no single base row (nor a single unambiguous set of them) that "set dept_id = 9" should hit, which is exactly the non-updatability condition. (An INSERT of (9) would fail for a second reason too: it must invent an employee row with unknown name, salary.)

Ex 6 — Cells F & F′: the join view, key-preserved vs not

Worked example Statement

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

emp_id is unique; each employee joins to exactly one department. Compare: (F) UPDATE emp_dept SET salary = 55000 WHERE emp_id = 7; (F′) UPDATE emp_dept SET dept_name = 'Sales' WHERE emp_id = 7; Which one can succeed on a modern engine, which cannot?

Forecast: guess which side of the join each column belongs to.

The geometry of a key-preserved join:

Figure 2 — Three unique employees rows each pair one-to-one with a view row (cyan arrows), so salary is writable. But a single departments row (amber) fans out to all three view rows, so dept_name has no unique reverse target and is not writable.

Find the key-preserved table. Each emp_dept row keeps emp_id unique, so every view row maps to exactly one employees row. employees is key-preserved. Why this step? Key-preservation is precisely the "one view row ↔ one base row" condition for the join case (cell F).
Case F — update a column of the key-preserved side. salary lives in employees. Since the map to that table is unique, the engine rewrites to UPDATE employees SET salary = 55000 WHERE emp_id = 7;. Why this step? Unique target row ⇒ well-defined write. Many engines (Oracle, SQL Server) accept this.
Case F′ — update a column of the other side. dept_name lives in departments. But many employees share one department, so one departments row backs many view rows — not key-preserved (cell F′). Changing dept_name here would silently rename the department for everyone. The engine rejects it. Why this step? The departments side is one→many; no unique reverse arrow.
Portability caveat. Standard SQL permits engines to reject even case F. So treat join-view writes as engine-dependent, or use an INSTEAD OF trigger.

Ex 7 — Cell G: UNION is a set operation

Worked example Statement

CREATE VIEW everyone AS
SELECT emp_id, name FROM current_staff
UNION
SELECT emp_id, name FROM alumni;

Run INSERT INTO everyone (emp_id, name) VALUES (200, 'Zoe'); Which base table receives the row?

Forecast: current_staff, alumni, both, or neither?

Look at where a row could come from. A UNION row could originate in current_staff, or alumni, or both (duplicates removed). Why this step? The view row does not remember its source table — the mapping is not to a single table (cell G).
Try to route the insert. Should (200, 'Zoe') go into current_staff, alumni, or both? Undefined. Why this step? No unique base table ⇒ no rewrite target.
Reject. The engine refuses the insert. Why this step? UNION is a set operation: it merges two result sets and, crucially, removes duplicate rows, so a single view row may correspond to a row in the first branch, the second branch, or both at once. That destroys the one-view-row ↔ one-base-row pairing — the mapping is now to an unknown table, and there is no unambiguous place to send the write. (Note: UNION ALL keeps duplicates and some engines allow writes through it when the branches are provably disjoint, so each row's source table is recoverable — but plain UNION de-duplicates and is never updatable.)

Ex 8 — Cell H: rescuing a join view with INSTEAD OF

Worked example Statement

You really need to insert through the join view emp_dept from Ex 6. You attach:

CREATE TRIGGER emp_dept_ins
INSTEAD OF INSERT ON emp_dept
FOR EACH ROW
BEGIN
  INSERT INTO employees(emp_id, name, salary, dept_id)
  VALUES (NEW.emp_id, NEW.name, NEW.salary, NEW.dept_id);
END;

Insert (300, 'Ken', 48000, 3, 'Ops') through the view, assuming department 3 already exists in departments. What actually gets written, and to which table?

Forecast: which of the five view columns become a real employees row?

Understand what the trigger replaces. INSTEAD OF means "don't try your automatic rewrite — run my body instead". Why this step? You are supplying the missing map the engine could not infer (see Triggers).
Follow the body. It inserts only into employees, using NEW.emp_id=300, NEW.name='Ken', NEW.salary=48000, NEW.dept_id=3. The dept_name='Ops' value is ignored — departments is left untouched. Why this step? You chose to treat departments as reference data, not a write target.
Mind the foreign key. employees.dept_id almost always references departments.dept_id. Since we are not creating a department, dept 3 must already exist in departments, or the insert fails on the foreign-key constraint. Why this step? The trigger writes only one table; it does not conjure the referenced parent row, so referential integrity is your responsibility (see Normalization).
Result. With dept 3 present, exactly one row is added to employees; zero rows added to departments. If dept 3 did not exist, the whole insert is rejected by the constraint.

Ex 9 — Cell I: real-world row-level security

Worked example Statement

Each manager should see and edit only their own department's employees. Build:

CREATE VIEW my_team AS
SELECT emp_id, name, salary, dept_id
FROM employees
WHERE dept_id = 3
WITH CHECK OPTION;

The manager of dept 3 runs UPDATE my_team SET salary = 62000 WHERE emp_id = 12; then tries UPDATE my_team SET dept_id = 4 WHERE emp_id = 12; Which update succeeds, which is blocked, and why?

Forecast: both succeed / first only / second only.

First update — inside the filter. Setting salary = 62000 for emp 12 keeps dept_id = 3 true, so WITH CHECK OPTION is satisfied. Rewrites to UPDATE employees SET salary = 62000 WHERE emp_id = 12;. Why this step? Plain base column, one↔one map (cell A/I), row stays in view — accepted.
Second update — pushes the row out. Setting dept_id = 4 would make dept_id = 3 false. That row would leave the view. WITH CHECK OPTION forbids a write that ejects the row. Why this step? This is the security point: a manager cannot "reassign an employee out of their own view" through it.
Result. Update 1 accepted (salary → 62000); update 2 rejected. Combined with GRANT SELECT, UPDATE ON my_team (see Database Security and GRANT), the manager is boxed into dept 3.

Ex 10 — Cell J: exam twist — DELETE vs boundary NULL

Worked example Statement

CREATE VIEW active AS
SELECT cust_id, name, status FROM customers WHERE status = 'A';

Start: customers has 10 rows and customer 5 has status = 'A'. Compare: (a) DELETE FROM active WHERE cust_id = 5; (b) UPDATE active SET status = NULL WHERE cust_id = 5; (no WITH CHECK OPTION) After each, how many rows does customers have, and does customer 5 appear in the view?

Forecast: does deleting through a view drop the real customer? Does NULL leave it visible?

Case (a) — DELETE propagates to base. The view has no data; DELETE FROM active rewrites to DELETE FROM customers WHERE cust_id = 5 AND status = 'A';. The real customer row is gone, so customers drops from 10 to 9 rows. Why this step? Writes always hit the base table — deleting through a view is not "hiding", it is destroying. This is cell J's first half: DELETE through a simple projection+WHERE view is fully updatable and cascades to the base row.
Case (b) — boundary NULL. Set status = NULL. In SQL, NULL = 'A' evaluates to UNKNOWN, not TRUE, so the view's WHERE status = 'A' excludes the row. The base row survives (still 10 rows) but vanishes from the view. Why this step? Three-valued logic: NULL never equals anything, so the row silently leaves the filter (like cell B, but via an update). Without WITH CHECK OPTION, this is allowed.
Contrast. (a) removes the base row entirely (10 → 9); (b) keeps it (still 10) but makes it invisible here. Same "disappears from view" outcome, completely different base-table reality — the exam trap.

Recall Self-test

Why is UPDATE dept_stats SET avg_pay = 60000 rejected? ::: AVG maps many base rows to one view row; no unique salary assignment yields a given average. An INSERT succeeds through cheap_items but the row isn't visible — why? ::: The WHERE filters reads only; the base insert landed outside it. Fix with WITH CHECK OPTION. On a join view, which column can you update? ::: One in the key-preserved table (unique reverse map), engine permitting. Does DELETE FROM active WHERE cust_id = 5 delete the real customer? ::: Yes — writes propagate to the base table; the row is destroyed, not hidden. Why does SET status = NULL hide a row from a status='A' view? ::: NULL = 'A' is UNKNOWN (not TRUE), so the row fails the filter.

See also Query Optimization and View Merging for how these rewrites are executed, and Normalization for why base tables are split (making join-views common in the first place).