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: INSERT‑SELECT)
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: UPDATE‑FROM)
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: UPDATE‑WHERE 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: UPDATE‑PRIMARY 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