17.00 - Atomic Upsert Examples - Parallel Data Pump

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K

This section describes several examples that demonstrate how the Atomic upsert feature works, including cases where errors are detected and returned to the user. All of the examples use the same table, called Sales, as shown below:

CREATE TABLE Sales, FALLBACK,
(ItemNbr    INTEGER NOT NULL,
SaleDate   DATE FORMAT 'MM/DD/YYYY' NOT NULL,
ItemCount  INTEGER)
PRIMARY INDEX   (ItemNbr);

It is assumed that the table has been populated with the following data:

INSERT INTO Sales (10, '05/30/2005', 1);

Assume that there exists a table called NewSales that has the same column definitions as those of table Sales.

Example 1 (Error: Different Target Tables)

This example demonstrates an upsert statement that does not specify the same table name for the UPDATE part and the INSERT part of the statement.

.Dml label upsertdml do insert for missing update rows;
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005');
INSERT INTO NewSales (10,'05/30/2005', 1);

A rule of an upsert statement is that only one single table is processed for the statement. Because the tables, Sales and NewSales, are not the same for the upsert statement, an error is returned, indicating that the name of the table must be the same for both the UPDATE and the INSERT.

Example 2 (Error: Different Target Rows)

This example demonstrates an upsert statement that does not specify the same primary index value for the UPDATE part and the INSERT part of the statement.

.Dml label upsertdml do insert for duplicate update rows;
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005');
INSERT INTO Sales (20,'05/30/2005', 1);

The primary index values for the UPDATE and the INSERT must be the same. Otherwise, we would be looking at two different rows: one for UPDATE and the other for INSERT, which is not the purpose of an upsert. An error is returned for the upsert statement because the specified primary index values of 10 and 20 are not the same (the primary index value must be the same for both the UPDATE and the INSERT).

Example 3 (Valid Upsert UPDATE)

This example demonstrates a successful upsert statement where a row gets updated.

.Dml label upsertdml do insert for missing update rows;
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005');
INSERT INTO Sales (10, '05/30/2005', 1);

After all of the rules have been validated, the row with ItemNbr = 10 and SaleDate = '05/30/2005' gets updated. A successful update of one row is returned.

Example 4 (Valid Upsert INSERT)

This example demonstrates a successful upsert statement where a row gets inserted.

.Dml label upsertdml do insert for missing update rows;
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 20 AND SaleDate = '05/30/2005')
INSERT INTO Sales (20, '05/30/2005', 1);

After all of the rules have been validated and no row was found with Item = 20 and SaleDate = '05/30/2005' for the UPDATE, a new row is inserted with ItemNbr = 20. A successful insert of one row is returned.