Deleting 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 delete table rows using a view through which the table is accessed, refer to the following conditions.
  • You must have the DELETE privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the DELETE privilege on the underlying object (view or base table) whose rows are to be deleted, 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 both the view definition and in the base table to which the view refers.

    While it is true that you can generally convert the type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then that index is not used to delete rows from the base table because the data type of the recast column no longer matches the data type definition for that column in the index.

    The resulting all-AMP, all-row behavior of the delete circumvents the performance advantages for which the index was designed.

  • Any two view columns cannot reference the same table column.
  • The view cannot include a column that contains a range constraint.
  • The expression used to define a view cannot have a data type specified for any column in the view.