21.5.3 Updatable and insertable views

Http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

Some views is updatable and references to them can is used to specify tables to being updated in data change statements . That's, you can use them in statements such asUPDATE,DELETE, orINSERTTo update the contents of the underlying table. Derived tables can also be specified in multiple-tableUPDATEandDELETEStatements, but can is used for the reading data to specify rows to be updated or deleted. Generally, the view references must is updatable, meaning that they is merged and not materialized. Composite views has more complex rules.

For a view to be updatable, there must is a one-to-one relationship between the rows in the view and the rows in the Underlying table. There is also certain other constructs a view nonupdatable. To BES more specific, a view is not updatable if it contains any of the following:

• Aggregate functions (,,,, SUM() MIN() MAX() COUNT() and so forth)

• DISTINCT

• GROUP BY

• HAVING

• union or            union all         

•           subquery in the Select list         

            before MySQL 5.7.11, subqueries in the select list fail for           insert , but is okay for           update ,           delete . As of MySQL 5.7.11,          that's still true for nondependent subqueries . For dependent          subqueries of 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, refers to a table in the FROM clause

• Refers literal values (in this case, there was no underlying table to update)

• ALGORITHM = TEMPTABLE(use of a temporary table always makes a view nonupdatable)

• Multiple references to all column of a base table (fails INSERT for, okay for UPDATE , DELETE )

21.5.3 updatable and insertable views