Updatable Views - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

Not all valid view definitions are updatable. In other words, there are several types of view definitions that do not permit you to perform DELETE, INSERT, or UPDATE operations against them. The general rule for updatability of a view is that there must be a 1:1 correspondence between a row defined in a view and the same row defined in an underlying base table on which that view is defined. If that 1:1 correspondence does not exist, then the view is not updatable.

The following features, when specified as a component of a view definition, automatically make that view non-updatable, or read-only.

  • Expressions
  • Joins

    If a view definition specifies more than one table, it is not updatable.

  • Any form of derived column
  • Any form of aggregation, including the following specific types:
    • Ordered analytical functions
    • Aggregate functions
  • Any form of aggregation-related clauses, including the following:
    • GROUP BY clause
    • HAVING clause
    • QUALIFY clause
  • Set operators, including all forms of the following:
    • EXCEPT and MINUS
    • INTERSECT
    • UNION
  • A TOP n or TOP m PERCENT clause
  • The DISTINCT operator
  • A WHERE clause that specifies a nested table expression that references the same table as is referenced by the main WHERE clause for the view definition

You also cannot execute any of the following HELP statements against a non-updatable view.

  • HELP CONSTRAINT
  • HELP INDEX
  • HELP STATISTICS