When Can You Update Through a View?

The rule is about the shape of the view. A view that maps rows one-to-one onto rows of a single base table is a candidate for writing: a simple projection of some columns of one table, with no aggregation, no join, no DISTINCT, no GROUP BY, no set operation and no window function. A view whose rows do not correspond one-to-one to base rows cannot be written to.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL,
  internal_note TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Ada Lovelace',   '[email protected]'),
  (2, 'Grace Hopper',   '[email protected]'),
  (3, 'Alan Turing',    '[email protected]');

-- A simple projection of one table: a candidate for writing.
CREATE VIEW customer_directory AS
SELECT id, name, email FROM customers;

-- An aggregate: one group row stands for many base rows, so it cannot be written.
CREATE VIEW customer_name_counts AS
SELECT name, COUNT(*) AS rows_with_name
FROM customers
GROUP BY name;

SELECT name FROM sqlite_master WHERE type = 'view' ORDER BY name;

Two rows come back: customer_directory and customer_name_counts. Nothing in the catalogue flags which one is writable — you have to read the definition and see the shape.

The reason an aggregate blocks a write is fundamental rather than a limitation. A group row represents many base rows, so there is no single row for the engine to write to, and choosing which of the hundred rows in a group to update would be arbitrary. The same reasoning applies to a join, where the output row is a combination of rows from two tables.

There is an important dialect difference here. PostgreSQL, MySQL and SQL Server treat a simple projection of one table as automatically updatable — you write to the view and the base table changes. SQLite does not: its views are always read-only, and to make one writable you attach an INSTEAD OF trigger that performs the write against the base table instead. The next answer shows that pattern, and it is worth knowing because the same trigger also works on PostgreSQL and MySQL when you want to add validation logic to the write.

SQLite views are read-only, so you attach an INSTEAD OF trigger. When the statement is aimed at the view, the trigger fires instead and performs the equivalent write against the base table.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL,
  internal_note TEXT
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Ada Lovelace', '[email protected]'),
  (2, 'Grace Hopper', '[email protected]');

CREATE VIEW customer_directory AS
SELECT id, name, email FROM customers;

CREATE TRIGGER trg_directory_update
INSTEAD OF UPDATE ON customer_directory
BEGIN
  UPDATE customers
  SET name = NEW.name,
      email = NEW.email
  WHERE id = NEW.id;
END;

CREATE TRIGGER trg_directory_insert
INSTEAD OF INSERT ON customer_directory
BEGIN
  INSERT INTO customers (id, name, email)
  VALUES (NEW.id, NEW.name, NEW.email);
END;

-- Write through the view.
UPDATE customer_directory
SET email = '[email protected]'
WHERE id = 1;

INSERT INTO customer_directory (id, name, email)
VALUES (3, 'Alan Turing', '[email protected]');

SELECT id, name, email FROM customers ORDER BY id;

Three rows come back. Ada's email is updated to [email protected], and Alan Turing has been added with id 3. Both writes went through the view and landed in the base table.

The mechanics are worth understanding. INSTEAD OF means the original operation is not performed at all — the engine runs your trigger body instead. NEW refers to the row as it would be after the statement, and OLD to the row as it is now, so the trigger can compare them and decide what to do. That is also what makes the trigger a useful place for validation: you can raise an error, or silently skip a row, before the base table is touched.

Three constraints to know. You can only write to the columns the view exposes, so an attempt to set internal_note through customer_directory fails because that column is not in the view. The trigger body is ordinary SQL, so it inherits all of the base table's constraints and triggers — a duplicate id still fails. And a view with a WHERE clause restricts the rows the write can reach, and with SQLite's WITH CHECK OPTION an insert of a row that would not match the filter is rejected.

On PostgreSQL, MySQL and SQL Server you would not need the trigger for a simple projection, because those engines make such views automatically updatable. The trigger is still the way to add behaviour that the plain update would not have, such as writing an audit row or normalising a value before it lands.