Rules for Updating Rows Using Views - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

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
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
To update rows using a view through which the table is accessed, observe the following rules:
  • You must have the UPDATE privilege on the view.

    The immediate owner of the view (that is, the containing database for the view) must have the UPDATE privilege on the underlying object (view, base table, or columns) whose columns are to updated, and the SELECT privilege on all tables that are specified in the WHERE clause.

  • Each column of the view must correspond to a column in the underlying table (that is, none of the columns in the view can be derived using an expression).
  • The data type definitions for an index column should match in the view definition and in the base table to which the view refers.

    Although you can generally convert the data type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then the Optimizer does not use that index when the base table is updated because the data type of the recast column no longer matches the data type of the index column.

    The resulting all-AMP, all-row scan defeats the performance advantages the index was designed for.

  • No two view columns can reference the same table column.
  • If the request used to define a view contains a WHERE clause WITH CHECK OPTION, then all values inserted through that view must satisfy the constraints specified in the WHERE clause.
  • If a view includes a WHERE clause and does not specify WITH CHECK OPTION, then data can be inserted through the view that will not be visible through that view.