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 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.
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 2 (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 3 (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 4 (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 5 (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 6 (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 7 (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 8 (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 9 (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 10 (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.