17.10 - Updatable Views - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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