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.