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

When the DEFAULT function is used for either the UPDATE operation or for the INSERT operation within the upsert, it evaluates to the default value of the referenced column. This is a Teradata extension.

Assume the following table definition for the examples:

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

     UPDATE table19
       SET col_2 = DEFAULT
         WHERE col1 = 10
       ELSE INSERT table_19 (10, DEFAULT, DEFAULT, 'aaa');

This request updates col_2 to the DEFAULT value of col_2, which is 10, depending on whether the WHERE condition evaluates to true or not.

If the row does exist, the updated row becomes the following: (10, 10, existing value, existing value).

If the row does not exist, the system inserts a new row with the a col_2 value of 10 (the default value of col_2) and a col_3 value of 20 (the default value of col_3). The newly inserted row is as follows: (10, 10, 20, 'aaa').

The following example is a correct use of the DEFAULT function within an UPDATE upsert request:

     UPDATE table_19
       SET col_2 = DEFAULT(col3)
         WHERE col_1 = 10
       ELSE INSERT table_19 (10, DEFAULT, DEFAULT(col_2), 'aaa');

When the value of col_1 is 10, this upsert updates col_2 to the DEFAULT value of col_3, which is 20, because the column name passed as the argument of the DEFAULT function is col_3.

If the row does exist, the updated row becomes the following: (10, 20, existing value, existing value).

If the row does not exist, the system inserts a new row with a col_2 value of 10 (default value of col_2) and a col_3 value of 10 (the default value of col_2). The newly inserted row is as follows: (10, 10, 10, 'aaa').