Inserting, Updating, and Deleting Values in Views

When you perform any of the above operations on a view, the changes are transferred to the base table that contains the data. Such operations are permitted only if the changes that must be made to the underlying table are unambiguous. The principle is that an insertion or change to one row in the view must translate to an insertion or change to one row in the leaf underlying table. If this is the case, the view is said to be updatable. The specific conditions outlined in the standard for a view to be updatable are:

  • It must be drawn on only one simply underlying table; joins are not allowed
  • It must contain only one query
  • If the simply underlying table is itself a view, that view must also be updatable
  • The SELECT clause of the contained query may specify only column references, not value expressions or aggregate functions, and no column can be referenced more than once
  • The contained query cannot specify GROUP BY or HAVING
  • The contained query cannot specify DISTINCT
  • Subqueries are permissible, but only if they do not refer to any of the generally underlying tables on which the view is based