Updatable and Insertable Views
1702
•
DISTINCT
•
GROUP BY
•
HAVING
•
LIMIT
•
UNION
or
UNION ALL
• Subquery in the select list
• Refers only to literal values (in this case, there is no underlying table)
18.4.3. Updatable and Insertable Views
Some views are updatable. 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()
[972]
,
MIN()
[971]
,
MAX()
[971]
,
COUNT()
[970]
, 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)
• Nonupdatable view in the
FROM
clause
• A 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)
• Uses
ALGORITHM = TEMPTABLE
(use of a temporary table always makes a view nonupdatable)
• Multiple references to any column of a base table.
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 and not derived columns. A derived column is
one that is not a simple column reference but is derived from an expression. These are examples of
derived columns:
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(
subquery
)
A view that has a mix of simple column references and derived columns is not insertable, but it can be
updatable if you update only those columns that are not derived. Consider this view:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...