Part 3 of 5 · 1 min read
Updatable views
When a view can accept INSERT, UPDATE or DELETE, and when it cannot.
Think of a saved search that shows emails matching a filter. Deleting one of those emails from the search view deletes the actual email too, since the view is just a lens onto real underlying data.
In plain words: a simple view, built from one table with no aggregation, grouping or set operation, is usually updatable: a change through the view becomes a change to the underlying table. A view built from a join, an aggregate, or a set operation is usually not updatable, since the database cannot always tell which underlying row a change should apply to.
A worked example. pune_customers, built from one table with a simple filter, can accept an UPDATE that changes a customer's name, which updates the real row in customers.
The key lines.
UPDATE pune_customers SET full_name = 'Asha R.' WHERE customer_id = 107;
-- this actually updates the row in the underlying customers table
Updatable view
An updatable view is a view simple enough that a change made through it can be applied unambiguously to its underlying table.
Example: a view with a GROUP BY is not updatable, since one view row can represent many underlying rows, with no single correct row to apply an update to.