Updating Data Through Views

Considerations for updating data through views.

You cannot update views defined with the distinct clause.
If a view is created with check option, each row that is updated through the view must remain visible through the view. For example, the stores_cal view includes all rows of the stores table where state has a value of “CA”. The with check option clause checks each update statement against the view’s selection criteria:
```
create view stores_cal
as select * from stores
where state = "CA"
with check option
```
An update statement such as this one fails if it changes state to a value other than “CA”:
```
update stores_cal
set state = "WA"
where store_id = "7066"
```
If a view is created with check option, all views derived from the base view must satisfy the view’s selection criteria. Each row updated through a derived view must remain visible through the base view.
Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of “Net 30”:
```
create view stores_cal30
as select * from stores_cal
where payterms = "Net 30"
```
Because stores_cal was created with check option, all rows updated through stores_cal30 must remain visible through stores_cal. Any row that changes state to a value other than “CA” is rejected.
Notice that stores_cal30 does not have a with check option clause of its own. Therefore, you can update a row with a payterms value other than “Net 30” through stores_cal30. For example, the following update statement would be successful, even though the row would no longer be visible through stores_cal30:
```
update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"
```
You cannot update a row through a view that joins columns from two or more tables, unless both of the following conditions are true:
- The view has no with check option clause, and
- All columns being updated belong to the same base table.
update statements are allowed on join views that contain a with check option clause. The update fails if any of the affected columns appear in the where clause in an expression that includes columns from more than one table.
If you update a row through a join view, all affected columns must belong to the same base table.