Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 30.4Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 182.5Kb
Man Pages (Zip) - 293.9Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

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

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

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 (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

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:

  • 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, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table 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 such UPDATE, DELETE, and INSERT are illegal and are rejected. (Note that 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 the updatable_views_with_limit system variable. See Section 5.1.5, “Server System Variables”.

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 because col2 is an expression. But it is updatable if the update does not try to update col2. 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;

For a multiple-table updatable view, INSERT can work if it inserts into a single table. DELETE is not supported.

INSERT DELAYED is not supported for views.

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


User Comments
  Posted by Gregory Weaver on August 13, 2009
One of the limitations above is that you cannot have duplicate column names. This does not make sense as you already cannot have duplicate column names. What I think they are trying to convey is this:
You cannot have duplicate base columns in the view for it to be insertable.

Example:
mysql> use world
Database changed
mysql> CREATE TABLE `city1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM
Query OK, 0 rows affected (0.03 sec)

mysql> create view city_v1 as select ID as ID1, ID as ID2, Name from city;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into city_v1 (ID1,Name) VALUES (10000,'Blah');
ERROR 1471 (HY000): The target table city_v1 of the INSERT is not insertable-into

  Posted by Jannes Faber on October 31, 2013
If a VIEW is not-updatable because of something in the WHERE clause (use of a function for example) then there's a trick to make it updatable again.

This is not-updatable:

CREATE OR REPLACE ALGORITHM=MERGE VIEW user1 AS
SELECT u1.*
FROM users u1
WHERE u1.ID=CurrentUserID()
AND u1.Deleted=0
-- WITH CHECK OPTION -- not possible
;

However, by moving the WHERE clause to an INNER JOIN it does work:

CREATE OR REPLACE ALGORITHM=MERGE VIEW user1 AS
SELECT u1.*
FROM users u1
INNER JOIN tradingsystem.users u2 ON u2.ID=u1.ID AND u2.ID=CurrentUserID()
WHERE u1.Deleted=0
WITH CHECK OPTION
;

Not it's perfectly updatable, except trying to change the ID or Deleted field would result in "Error Code: 1369. CHECK OPTION failed 'user1'". Which is as expected.

The downside is of course the extra JOIN, but as we're talking about primary keys here that should be pretty quick (eq_ref) or (in my case) be optimized as "const" anyway. Check EXPLAIN SELECT * FROM user1; for yourself.
Sign Up Login You must be logged in to post a comment.