15.00 - Atomic Upsert Examples - Parallel Data Pump

Teradata Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Atomic Upsert Examples

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);

Assume that the table has been populated with the following data:

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

A table called NewSales has the same column definitions as those of table Sales.

Example (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.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO NewSales (10, '05/30/2005', 1);

A rule of an upsert 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 to the user indicating that the name of the table must be the same for both the UPDATE and the INSERT.

Example (Error: different target rows)

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

UPDATE Sales SET ItemCount = Itemcount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (20, '05/30/2005', 1);

The primary index values for the UPDATE and the INSERT must be the same. In this case, an error is returned to the user indicating that the primary index value must be the same for both the UPDATE and the INSERT.

Example (Error: unqualified primary index)

This example demonstrates an upsert statement that does not specify the primary index in the WHERE clause.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE SaleDate = '05/30/2005' ELSE INSERT INTO Sales (10, '05/30/2005', 1);

When the primary index is not fully specified in the UPDATE of an upsert statement, an all‑row scan to find rows to update might result. This is again not the purpose of upsert, and an error is returned to the user.

Example (Error: missing ELSE)

This example demonstrates an upsert statement with a missing ELSE keyword.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') INSERT INTO Sales (10, '05/30/2005', 1);

Example (Error: INSERTSELECT)

This example demonstrates an upsert statement that specifies INSERT … SELECT.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales SELECT * FROM NewSales WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005');

The INSERT part of an upsert may not use a subquery to specify any of the inserted values. In this case, a syntax error is returned.

Example (Error: UPDATEFROM)

This example demonstrates an upsert statement that specifies UPDATE‑FROM.

UPDATE Sales FROM NewSales SET Sales.ItemCount = NewSales.ItemCount WHERE Sales.ItemNbr = NewSales.ItemNbr ELSE INSERT INTO Sales (10, '05/30/2005', 1);

The SET clause may not use a FROM clause table reference in the expression for the updated value for a column, and an error is returned.

Example (Error: UPDATEWHERE SUBQUERIES)

This example demonstrates an upsert statement that specifies UPDATE‑WHERE SUBQUERIES.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE ItemNbr IN (SELECT ItemNbr FROM NewSales) ELSE INSERT INTO Sales (10, '05/30/2005', 1);

The WHERE clause of the UPDATE may not use a subquery for any purpose. In this case, error ERRTEQUPSCOM is returned.

Example (Error: UPDATEPRIMARY INDEX)

This example demonstrates an upsert statement that tries to update a primary index value.

UPDATE Sales SET ItemNbr = 20 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (20, '05/30/2005', 1);

Unreasonable updates or updates that change the primary index values are not allowed in an upsert statement, and an error is returned.

Example (Valid Upsert UPDATE)

This example demonstrates a successful upsert statement that updates a row.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE 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 results.

Example (Valid Upsert INSERT)

This example demonstrates a successful upsert statement that inserts a row.

UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 20 AND SaleDate = '05/30/2005') ELSE 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 results.

Version

Purpose  

The VERSION command, which is primarily for developer use, displays version information for each utility component, including TPump, Teradata ICU, Teradata CLI and Teradata Data Connector.

Syntax  

Example  

     TPMPVER = TLDMAIN  14.10.00.00 2012/01/24
     TPMPVER = TLDCNTL  14.10.00.06 2012/12/19
     TPMPVER = TLDEXEC  15.00.00.01 2013/06/24
     TPMPVER = TLDLANG  15.00.00.01 2013/06/27
     TPMPVER = TLDIPTSK 15.00.00.00 2013/12/17
     TPMPVER = TLDSTMTS 15.00.00.02 2013/08/01
     TPMPVER = TLDPREP  15.00.00.00 2014/01/09
     TPMPVER = TLDMISC  15.00.00.00 2013/12/17
     TPMPVER = TLDGLOBL 15.00.00.00 2013/09/03
     TPMPVER = TLDRCV   14.10.00.05 2012/12/11
     TPMPVER = TLDJNL   13.01.00.00 2008/09/03
     TPMPVER = TLISRATE 14.10.00.02 2012/12/11
     TPMPVER = TLISESS  15.00.00.00 2013/07/04
     TPMPVER = TLIDRV   15.00.00.02 2013/11/20
     TPMPVER = TLITUTIL 13.01.00.00 2008/09/03
     TPMPVER = TLIHASH  14.10.00.00 2012/04/05
     TPMPVER = TLISGEN  15.00.00.00 2013/08/14
     TPMPVER = TLICLI   14.10.00.09 2013/01/10
     TPMPVER = TLDNOTFY 14.10.00.04 2012/12/07
     UT$VER = UMBCNTLR 15.00.00.04 2013/12/02
     UT$VER = UMBEXEC  15.00.00.00 2013/06/27
     UT$VER = UMBSTFM  15.00.00.02 2013/10/05
     UT$VER = UMBSTLG 15.00.00.00 2013/06/27
     UT$VER = UMBSTQ   14.00.00.01 2011/05/26
     UT$VER = UMBSTX   13.01.00.00 2008/09/10
     UT$VER = UTCLI    15.00.00.10 2013/11/13
     UT$VER = UTYIO    15.00.00.06 2013/09/19
     UT$VER = UTYMSORT 14.00.00.01 2012/03/12
     UT$VER = UTYCONFG 14.10.00.00 2012/10/04
     UT$VER = UTYWAIT  14.10.00.02 2012/03/12
     UT$VER = UMBDLOC  15.00.00.07 2014/01/08
     UT$VER = UMBOSDEP 14.10.00.03 2012/03/12
     UT$VER = UTYMSG   14.10.00.01 2012/02/15
     UT$VER = UTYSMPR  15.00.00.02 2013/11/12
     UT$VER = UTYVSUB  15.00.00.04 2014/01/16
     UT$VER = UTYMBCS  14.10.00.03 2012/03/21
     UT$VER = UTYKUNX  14.10.00.00 2012/04/25
     UT$VER = UTYCVTR  15.00.00.00 2013/10/05
     UT$VER = UTYCRMGR 14.10.00.02 2012/03/12
     UT$VER = UTYPARSR 15.00.00.07 2013/12/23
     UT$VER = UTYMISC  15.00.00.02 2013/11/08
     UT$VER = UTYEXEVL 15.00.00.04 2013/11/11
     UT$VER = UTYEXPRS 15.00.00.03 2013/12/03
     UT$VER = UMBSTIN  15.00.00.04 2013/11/11
     UT$VER = UMBSTOP  15.00.00.01 2013/09/03
     UT$VER = UMUCNTIN 15.00.00.00 2013/05/15
     UT$VER = UMUOUT   14.00.00.03 2011/11/04
     UT$VER = UTYLOGW  13.01.00.00 2008/09/16
     UT$VER = UTYLOADM 14.10.00.03 2012/03/12
     UT$VER = UTYMVSQ  13.01.00.00 2008/09/10
     UT$VER = UTYNFY   14.10.00.00 2012/10/31
     UT$VER = UTYGETOP 14.10.00.00 2012/03/08
      CLIV2     : 15.00.00.00
      MTDP      : 14.10.00.28
      MOSIos    : 14.10.00.09
      MOSIDEP   : 14.10.00.06
      OSENCRYPT : N/A
      OSERR     : 14.00.00.00
     ICUVER = TDICU, 14.10b.00.00
     PMVER = Teradata Data Connector, 15.00.00.00
     PMVER = PMPROCS, 15.00.00.02
     PMVER = PMRWFMT, 15.00.00.00
     PMVER = PMTRCE, 13.10.00.02
     PMVER = PMMM, 13.00.00.01
     PMVER = PMUDDI, 15.00.00.00
     PMVER = DCUDDI, 15.00.00.02
     PMVER = PMHEXDMP, 14.10.00.00
     PMVER = PMUNXDSK, 14.10.00.07