Example: Update Requests Using the DEFAULT Function - 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-10-04
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantageā„¢

You can specify a DEFAULT function without a column name as the expression in the SET clause.

The column name for the DEFAULT function is the column specified as the column name. The DEFAULT function evaluates to the default value of the column specified as column_name.

The following examples assume the following table definition:

     CREATE TABLE table_11 (
       col_1 INTEGER,
       col_2 INTEGER DEFAULT 10,
       col_3 INTEGER DEFAULT 20,
       col_4 CHARACTER(60));

The following UPDATE requests are valid:

The following example updates the values of col3 to 20 (its default value) for all rows:

     UPDATE table_11
       SET col_3 = DEFAULT;

The following example updates the values of Col3 to 20 (its default value) for rows where the value of col1=5.

     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE Col1 = 5;

Assume the following table definition for the next example:

     CREATE TABLE table_12 (
       x INTEGER,
       y INTEGER);

The following example updates the values of col3 to 20 (its default value) depending on whether the WHERE condition evaluates to true or not.

     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE 5 < ANY
          (SELECT y
           FROM table_12);

You can specify a DEFAULT function with a column name in the source expression. The DEFAULT function evaluates to the default value of the column name specified as the input argument to the DEFAULT function. For example, DEFAULT(col_2) evaluates to the default value of col_2. This is a Teradata extension.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2. Therefore, the DEFAULT function evaluates to the default value of the col_2 and then set col_3 to this value. Specifically, it updates the values of col_3 to 10 (the default value of col_2) for all rows.

     UPDATE table_11
       SET col3 = DEFAULT(col_2);

The following example updates the values of col_3 to 10 (the default value of col_2) for rows where the value of col_1 is 5.

     UPDATE table_11
        SET col_3 = DEFAULT(col_2)
         WHERE col_1 = 5;

You can specify a DEFAULT function with a column name anywhere in the update expression. This is a Teradata extension to the ANSI SQL:2011 standard.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2; therefore, the DEFAULT function evaluates to the default value of col_2. The request then updates the value of col_3 to 15 (10+5, the default value of col_2 + 5) for all rows.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2) + 5;

The following example updates the value of col3 to 15 (the default value of col2+5) for all rows.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2) + 5 ALL;

The following example updates the values of col3 to 15 (the default value of col_2 + 5) for rows where the value of col_1=20.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2)+5
         WHERE col_1 = 20;

When there is no explicit default value associated with the column, the DEFAULT function evaluates to null.

Assume the following table definition for the examples that follow:

     CREATE TABLE table_13 (
       col_1 INTEGER,
       col_2 INTEGER NOT NULL,
       col_3 INTEGER NOT NULL DEFAULT NULL,
       col_4 INTEGER CHECK (col_4>100) DEFAULT 99 );

In the following example, col_1 is nullable and does not have an explicit default value associated with it; therefore, the DEFAULT function evaluates to null.

     UPDATE table_13
       SET col_1 = DEFAULT;

The following UPDATE requests are equivalent. For both requests, the DEFAULT function evaluates to the default value of col_3 for rows where the value of col_1 is 5.

     UPDATE table_11
       SET col_3 = DEFAULT(c3)
         WHERE col_1 = 5;
     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE col_1 = 5;