Which views can you write to, and which cannot?
You can write to a view that is a simple select over one table. You cannot write to one that works out values, and the rules are quite specific.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(200) NOT NULL
);
-- A simple view over one table. This one can be written to.
CREATE VIEW customer_names AS
SELECT id, name, email FROM customers;
So what makes a view writable? Four conditions, and all of them have to hold.
It has to be over one table. It cannot have a join, because the engine would not know which table to write to. It cannot group the rows or use an aggregate function, because the rows in the answer do not line up with the rows in the table. And it cannot use the word distinct, for the same reason.
this view can you write to it?
------------------------ --------------------------------
a select over one table yes
a select with a join no, not in most engines
a select with a group by no
a select with distinct no
a select over two tables no
There is one more thing that you have to watch. The columns of the view have to line up with the columns of the table, so that the engine knows which column each value belongs in. If the view renames a column with the word as, that is fine. If it puts an expression in the select list, then that column is not something that you can write to.
The next question in this topic shows a view that you can write through.