16.20 - Omitting the Column Name - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

You can use the form of DEFAULT that omits the column name under certain conditions in an INSERT, UPDATE, or MERGE statement or in a predicate clause that involves a comparison operation. The form of DEFAULT that omits the column name cannot be part of an expression.

When the DEFAULT function does not specify a column name, Teradata Database derives the column based on context. For example, consider the following table definition:

   CREATE TABLE Manager
      (Emp_ID      INTEGER
      ,Dept_No     INTEGER DEFAULT 99
   );

The following INSERT statement uses DEFAULT without a column name to insert the default value into the Dept_No column:

   INSERT INTO Manager VALUES (103499, DEFAULT);

Using the DEFAULT function without specifying a column name can produce an error if Teradata Database cannot derive the column context.

For an example that omits the column name when using the DEFAULT function in a predicate clause that involves a comparison operation, see Example: Using DEFAULT in a Predicate.

For details on using the DEFAULT function in INSERT, UPDATE, and MERGE statements, see Teradata Vantage™ - SQL Data Manipulation Language , B035-1146 .