MySQL :: MySQL 8.0 Reference Manual :: 25.5.3 Updatable and Insertable Views

version 8.0

5.7
5.6

8.0 Japanese
5.6 Japanese

MySQL 8.0 Reference Manual/.../ Updatable and Insertable Views

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 asUPDATE,DELETE, orINSERTto update the contents of the underlying table. Derived tables and common table expressions can also be specified in multiple-tableUPDATEandDELETEstatements, 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
UNIONorUNION ALL
Subquery in the select list
Nondependent subqueries in the select list fail forINSERT, but are okay forUPDATE,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 theFROMclause
Subquery in theWHEREclause that refers to a table in theFROMclause
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 forINSERT, okay forUPDATE,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 isDEFAULT. For information about generated columns, see创建表和生成坳13.1.20.8节。umns”.

It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with theMERGEalgorithm. For this to work, the view must use an inner join (not an outer join or aUNION). Also, only a single table in the view definition can be updated, so theSETclause must name only columns from one of the tables in the view. Views that useUNION ALLare not permitted even though they might be theoretically updatable.

With respect to insertability (being updatable withINSERTstatements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

There must be no duplicate view column names.
The view must contain all columns in the base table that do not have a default value.
The view columns must be simple column references. They must not be expressions, such as these:
```
3.14159 col1 + 3 UPPER(col2) col3 / col4 (subquery)
```

MySQL sets a flag, called the view updatability flag, atCREATE VIEWtime. The flag is set toYES(true) ifUPDATEandDELETE(and similar operations) are legal for the view. Otherwise, the flag is set toNO(false). TheIS_UPDATABLEcolumn in theINFORMATION_SCHEMA.VIEWStable displays the status of this flag. It means that the server always knows whether a view is updatable.

If a view is not updatable, statements suchUPDATE,DELETE, andINSERTare illegal and are rejected. (Even if a view is updatable, it might not be possible to insert into it, as described elsewhere in this section.)

The updatability of views may be affected by the value of theupdatable_views_with_limitsystem variable. SeeSection 5.1.8, “Server System Variables”.

For the following discussion, suppose that these tables and views exist:

CREATE TABLE t1 (x INTEGER); CREATE TABLE t2 (c INTEGER); CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1; CREATE VIEW vup AS SELECT * FROM t2; CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;

INSERT,UPDATE, andDELETEstatements are permitted as follows:

INSERT: The insert table of anINSERTstatement may be a view reference that is merged. If the view is a join view, all components of the view must be updatable (not materialized). For a multiple-table updatable view,INSERTcan work if it inserts into a single table.
This statement is invalid because one component of the join view is nonupdatable:
```
INSERT INTO vjoin (c) VALUES (1);
```
This statement is valid; the view contains no materialized components:
```
INSERT INTO vup (c) VALUES (1);
```
UPDATE: The table or tables to be updated in anUPDATEstatement may be view references that are merged. If a view is a join view, at least one component of the view must be updatable (this differs fromINSERT).
In a multiple-tableUPDATEstatement, the updated table references of the statement must be base tables or updatable view references. Nonupdated table references may be materialized views or derived tables.
This statement is valid; columncis from the updatable part of the join view:
```
UPDATE vjoin SET c=c+1;
```
This statement is invalid; columnxis from the nonupdatable part:
```
UPDATE vjoin SET x=x+1;
```
This statement is valid; the updated table reference of the multiple-tableUPDATEis an updatable view (vup):
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET c=c+1;
```
This statement is invalid; it tries to update a materialized derived table:
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ... SET s=s+1;
```
DELETE: The table or tables to be deleted from in aDELETEstatement must be merged views. Join views are not allowed (this differs fromINSERTandUPDATE).
This statement is invalid because the view is a join view:
```
DELETE vjoin WHERE ...;
```
This statement is valid because the view is a merged (updatable) view:
```
删除vup…;
```
This statement is valid because it deletes from a merged (updatable) view:
```
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
```

Additional discussion and examples follow.

Earlier discussion in this section pointed out that a view is not insertable if not all columns are simple column references (for example, if it contains columns that are expressions or composite expressions). Although such a view is not insertable, it can be updatable if you update only columns that are not expressions. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

This view is not insertable becausecol2is an expression. But it is updatable if the update does not try to updatecol2. This update is permissible:

UPDATE v SET col1 = 0;

This update is not permissible because it attempts to update an expression column:

UPDATE v SET col2 = 0;

If a table contains anAUTO_INCREMENTcolumn, inserting into an insertable view on the table that does not include theAUTO_INCREMENTcolumn does not change the value ofLAST_INSERT_ID(), because the side effects of inserting default values into columns not part of the view should not be visible.

PREV HOME UP NEXT