このセクションでは、Atomic UPSERT機能の仕組みを示す例(エラーが検出され、ユーザーに返される例など)をいくつか取り上げます。どの例にも、同じテーブル(Sales)を使用します。
CREATE TABLE Sales, FALLBACK, (ItemNbr INTEGER NOT NULL, SaleDate DATE FORMAT 'MM/DD/YYYY' NOT NULL, ItemCount INTEGER) PRIMARY INDEX (ItemNbr);
次のようなデータがテーブルに記入してあるとします。
INSERT INTO Sales (10, '05/30/2005', 1);
NewSalesと呼ばれるテーブルの列定義は、テーブルSalesと同じです。
例1 (エラー: 異なったターゲット テーブル)
ここでは、UPSERT文のUPDATE部とINSERT部に異なるテーブル名を指定した例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO NewSales (10, '05/30/2005', 1);
UPSERTには、同じ文では1つのテーブルしか処理できないという規則があります。このUPSERT文には、SalesおよびNewSalesという別々のテーブルが使用されているため、UPDATEとINSERTの両方に同じ名前のテーブルを指定しなければならないことを示すエラーが返されます。
例2 (エラー: 異なったターゲット行)
ここでは、UPSERT文のUPDATE部とINSERT部に異なる基本索引値を指定した例を示します。
UPDATE Sales SET ItemCount = Itemcount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (20, '05/30/2005', 1);
UPDATEとINSERTの基本索引値は同じでなければなりません。異なる値を指定した場合、UPDATEとINSERTの両方に同じ基本索引値を指定しなければならないことを示すエラーが返されます。
例3 (エラー: 基本索引が修飾されていない)
ここでは、基本索引値をWHERE句の中に指定しないUPSERT文の例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE SaleDate = '05/30/2005' ELSE INSERT INTO Sales (10, '05/30/2005', 1);
UPSERT文のUPDATEによる基本索引の指定が完全でない場合、すべての行をスキャンしない限り更新の対象となる行を検出できなくなる可能性があります。このような使い方もUPSERTの用途にそぐわないため、エラーが返されます。
例4 (エラー: ELSEがない)
ここでは、ELSEキーワードのないUPSERT文の例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') INSERT INTO Sales (10, '05/30/2005', 1);
例5 (エラー: INSERT-SELECT)
この例では、INSERT … SELECTを指定するUPSERT文を示します。
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');
UPSERTのINSERT部には、挿入値を指定する副問合わせを使用できません。使用した場合、構文エラーが返されます。
例6 (エラー: UPDATE-FROM)
ここでは、UPDATE-FROMを指定するUPSERT文の例を示します。
UPDATE Sales FROM NewSales SET Sales.ItemCount = NewSales.ItemCount WHERE Sales.ItemNbr = NewSales.ItemNbr ELSE INSERT INTO Sales (10, '05/30/2005', 1);
SET句では、列の値を更新する式の中にFROM句によるテーブル参照を使用することはできません。使用した場合はエラーが返されます。
例7 (エラー: UPDATE-WHERE SUBQUERIES)
ここでは、UPDATE-WHERE SUBQUERIESを指定するUPSERT文の例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE ItemNbr IN (SELECT ItemNbr FROM NewSales) ELSE INSERT INTO Sales (10, '05/30/2005', 1);
UPDATEのWHERE句には、副問合わせをどのような目的にも使用できません。使用した場合、エラーERRTEQUPSCOMが返されます。
例8 (エラー: UPDATE-PRIMARY INDEX)
ここでは、基本索引値の更新を試みるUPSERT文の例を示します。
UPDATE Sales SET ItemNbr = 20 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (20, '05/30/2005', 1);
基本索引値の変更を伴なう更新、または不当な更新(1つまたは複数)は、UPSERT文の中に使用できません。使用した場合はエラーが返されます。
例9 (有効なUPSERT文のUPDATE)
ここでは、行を正常に更新するUPSERT文の例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 10 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (10, '05/30/2005', 1);
規則がすべて検証された後、ItemNbr=10およびSaleDate='05/30/2005'を持った行が更新されます。結果として、1行が正常に更新されます。
例10 (有効なUPSERT文のINSERT)
ここでは、行を正常に挿入するUPSERT文の例を示します。
UPDATE Sales SET ItemCount = ItemCount + 1 WHERE (ItemNbr = 20 AND SaleDate = '05/30/2005') ELSE INSERT INTO Sales (20, '05/30/2005', 1);
規則がすべて検証された後、UPDATE用のItem=20およびSaleDate='05/30/2005'の行が見つからなければ、ItemNbr=20の新しい行が挿入されます。結果として、1行が正常に挿入されます。