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” . 

## 25.5.3 Updatable and Insertable Views 

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: 

•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 

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. 

•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 ) 

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: 

