Updatable and Insertable Views Examples are  SELECT DISTINCT  or  LIMIT  in the subquery. For details, see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” . Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as  UPDATE ,  DELETE , or  INSERT  to update the contents of the underlying table. Derived tables and common table expressions can also be specified in multiple-table  UPDATE  and  DELETE  statements, but can only be used for reading data to specify rows to be updated or deleted. Generally, the view references must be updatable, meaning that they may be merged and not materialized. Composite views have more complex rules. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following: Nondependent subqueries in the select list fail for  INSERT , but are okay for  UPDATE ,  DELETE . For dependent subqueries in the select list, no data change statements are permitted. A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is  DEFAULT . For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns” . It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the  MERGE  algorithm. For this to work, the view must use an inner join (not an outer join or a  UNION ). Also, only a single table in the view definition can be updated, so the  SET  clause must name only columns from one of the tables in the view. Views that use  UNION ALL  are not permitted even though they might be theoretically updatable. With respect to insertability (being updatable with  INSERT  statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns: 25.5.3 Updatable and Insertable Views •Aggregate functions or window functions ( SUM() ,  MIN() ,  MAX() ,  COUNT() , and so forth) • DISTINCT • GROUP BY • HAVING • UNION  or  UNION ALL •Subquery in the select list •Certain joins (see additional join discussion later in this section) •Reference to nonupdatable view in the  FROM  clause •Subquery in the  WHERE  clause that refers to a table in the  FROM  clause •Refers only to literal values (in this case, there is no underlying table to update) • ALGORITHM = TEMPTABLE  (use of a temporary table always makes a view nonupdatable) •Multiple references to any column of a base table (fails for  INSERT , okay for  UPDATE ,  DELETE ) 4747