This section provides examples that show how the atomic upsert form of UPDATE works, including error cases. All examples use the same table called sales, which is defined as:
CREATE TABLE sales, FALLBACK item_nbr INTEGER NOT NULL, sale_date DATE FORMAT 'MM/DD/YYYY' NOT NULL, item_count INTEGER) PRIMARY INDEX (item_nbr);
Assume that the table has been populated with the following data.
INSERT INTO sales (10, '05/30/2000', 1);
Example: Upsert Update
This example shows a valid upsert UPDATE request.
UPDATE sales SET itemcount = item_count + 1 WHERE (item_nbr = 10 AND sale_date = '05/30/2000') ELSE INSERT INTO sales (10, '05/30/2000', 1);
After all of the rules have been validated, the row with item_nbr = 10 and sale_date = ‘05/30/2000’ gets updated.
A message indicates the successful update of one row.
Example: Upsert Insert
This example shows a valid upsert INSERT request.
UPDATE sales SET item_count = item_count + 1 WHERE (item_nbr = 20 AND sale_date = '05/30/2000') ELSE INSERT INTO sales (20, '05/30/2000', 1);
After all of the rules have been validated and no row was found that satisfies the compound predicate item = 20 and sale_date = ‘05/30/2000’ for the update, a new row is inserted with item_nbr = 20.
A message indicates the successful insert of one row.
Example: Upsert Specifying Different Tables
This example shows an upsert UPDATE request that does not specify the same table name for both the UPDATE part and the INSERT part of the request.
UPDATE sales SET item_count = item_count + 1 WHERE (item_nbr = 10 AND sale_date = '05/30/2000') ELSE INSERT INTO new_sales (10, '05/30/2000', 1);
One of the rules of the upsert form of UPDATE is that only one table is processed for the request. Because the tables sales and new_sales are not the same for this example the system returns an error to the user indicating that the name of the table must be the same for both the UPDATE and the INSERT.
Example: Upsert on Primary Index
This example shows an upsert UPDATE request that does not specify the same primary index value for the UPDATE and INSERT parts of the request.
UPDATE sales SET item_count = item_count + 1 WHERE (item_nbr = 10 AND sale_date = '05/30/2000') ELSE INSERT INTO sales (20, '05/30/2000', 1);
The primary index value specified for the UPDATE and the INSERT must be the same. Otherwise, the operation is looking at two different rows: one for UPDATE and the other for the INSERT. This is not the purpose of the upsert form of UPDATE.
Because the specified primary index values of 10 and 20 are not the same, this case returns an error to the user, indicating that the primary index value must be the same for both the UPDATE and the INSERT.
Example: Upsert Without Specifying Primary Index
This example shows an upsert UPDATE request that does not specify the primary index in its WHERE clause.
UPDATE sales SET item_count = item_count + 1 WHERE sale_date = '05/30/2000' ELSE INSERT INTO sales (10, '05/30/2000', 1);
When the primary index is not specified in the UPDATE portion of an upsert request, the operation could have to perform an all-row scan to find rows to update. This is not the purpose of upsert form of UPDATE. This case returns an error.
Example: Upsert Without ELSE Clause
This example shows an upsert UPDATE request that fails to specify the ELSE keyword.
UPDATE sales SET item_count = item_count + 1 WHERE (item_nbr = 10 AND sale_date = '05/30/2000') INSERT INTO sales (10, '05/30/2000', 1);
This case returns a syntax error to the user.
Example: Upsert Update Using the DEFAULT Function
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').
Example: Upsert Update Using a Period Bound Function
Suppose you define the following row-partitioned table using the END Period bound function.
CREATE SET TABLE testing.t33 ( a INTEGER, b PERIOD(DATE), c INTEGER) PRIMARY INDEX (a) PARTITION BY CAST((END(b)) AS INTEGER);
This UPDATE upsert request inserts a new row into t33.
UPDATE t33 SET c = 1 WHERE a = 20 AND END(b) = DATE '1901-02-25' ELSE INSERT INTO t33 (20, PERIOD(DATE '1901-02-24', DATE '1901-02-25'), 1);