Example: Update Requests Using the DEFAULT Function - Advanced SQL Engine - Teradata Database

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 examples below 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;