このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。
この記事のオリジナルは、Glenn Paulley が sybase.com に 2010 年 1 月に掲載したものです。その中で、Glenn は SQL Anywhere の version 12 で追加された機能である、様々なDML クエリに対する SELECT 文の使用について継続して解説しています。
以前掲載した ブログ記事において、SQL Anywhere 12 サーバーから実装された新しい SQL 言語の機能である dml-derived-table からの行の
SELECT 方法例を紹介しました。今回は、アプリケーションをシンプルにする -derived-tables のその他の方法について簡単に紹介したいと思います。
まず最初に、このブログのタイトルは「SELECT 文内での UPDATE 文の使用」ですが、dml-derived-tables は、派生テーブルであるため、もちろん、DML 文 (
INSERT, MERGE, DELETE, UPDATE) を含む派生テーブルを使用するようなあらゆるコンテキストに使用することができます。その結果として、他の中の 1つの
UPDATE 文を効果的に「入れ子」にすることができます。以下は、
UPDATE との組み合わせで MERGE を使用している例です。- CREATE TABLE modified_employees
- ( EmployeeID INTEGER PRIMARY KEY, Surname VARCHAR(40), GivenName VARCHAR(40) )
- MERGE INTO modified_employees AS me
- USING (SELECT modified_employees.EmployeeID,
- modified_employees.Surname,
- modified_employees.GivenName
- FROM (
- UPDATE Employees
- SET Salary = Salary * 1.03
- WHERE ManagerID = 501)
- REFERENCING (FINAL as modified_employees) ) AS dt_e
- on dt_e.EmployeeID = me.EmployeeID
- WHEN MATCHED THEN SKIP
- WHEN NOT MATCHED THEN INSERT
- OPTION(optimization_level=1, isolation_level=2)
上の例では、テーブル 「modified_employees」 は、情報が変更されている Employee の集まりをモデル化します。上記の
MERGE 文は、salary が 3%増加した社員の名前と EmployeeIDを、既に「modified_employees」テーブルに存在する社員データに merge します。2つ目に、上の13 行目の
OPTION 句に注目してください。この文で OPTION 句を使用して一時的に設定されているオプション設定は外側の MERGE だけではなく、入れ子となっている UPDATE 文にも適用されます。3つ目に、dml-derived-table は、単に派生テーブルなので、複数の dml-derived-tablescan が同じ SQL 文内に存在します。
以下は、Demo サンプルデータベース内の Products と SalesOrderItems のテーブルの独立した更新を結合し、これらの修正を含む join に基づいた結果を作成する一例です。
- SELECT old_products.ID, old_products.name, old_products.UnitPrice as OldPrice,
- final_products.UnitPrice as NewPrice,
- SalesOrders.ID as OrderID, SalesOrders.CustomerID,
- old_order_items.Quantity,
- old_order_items.ShipDate as OldShipDate,
- final_order_items.ShipDate as RevisedShipDate
- FROM
- ( UPDATE Products SET UnitPrice = UnitPrice * 1.07 )
- REFERENCING ( OLD AS old_products FINAL AS final_products )
- JOIN
- ( UPDATE SalesOrderItems
- SET ShipDate = DATEADD( DAY, 6, ShipDate )
- WHERE ShipDate BETWEEN '2000-04-10' and '2000-05-21' )
- REFERENCING ( OLD as old_order_items FINAL AS final_order_items )
- ON (old_order_items.ProductID = old_products.ID)
- JOIN SalesOrders ON ( SalesOrders.ID = old_order_items.ID )
- WHERE old_order_items.Quantity > 36
- ORDER BY old_products.ID
複数の dml-derived-table が存在する場合には、それぞれの update 文の実行順序は、実装定義型であり、保証されません。
グラフィカルなプランでは、dml-derived-tableは、アクセスプランツリー内の「DML」ノードで示されます。
そして、「DML」ノードで表されるステートメントのプランは、サブクエリーとしてリストされます。
最後に、SQL Anywhere 12 GA のリリースは、dml-derived-table としての update 文の埋め込みをサポートしています。しかしながら、その結果のマテリアライズは実行しません。
この場合、修正したデータの結果は同じSQL 文内で、outer blocksで利用可能ではないことを示すために、構文
REFERENCING( NONE )を使用します。dml-derived-table の SQL 文法は以下のとおりです。
- <table primary> ::= <table or query name> [ [ AS ] <correlation name> [ ( <derived column list> ) ] ]
- | other forms of table references ...
- | ( <dml derived table> ) REFERENCING <result option>
- <dml derived table> ::= <delete statement>
- | <insert statement>
- | <merge statement>
- | <update statement>
- <result option> ::= OLD [AS] <correlation name>
- | FINAL [AS] <correlation name>
- | OLD [AS] <correlation name> FINAL [AS] <correlation name>
- | NONE
REFERENCING(NONE) を使用する場合、update 文の結果は、マテリアライズされていません。そのため、空です。dml-derived-table は空のため、入れ子となる文を注意深く作成して、意図した結果が確実に返るようにする必要があります。
サーバーは、即時マテリアライズドビューのメンテナンスのために
REFERENCING(NONE) を内部で使用します。アプリケーション内では、outer join でNULLのデータが入っている側（RIGHT OUTER JOIN であれば右側）内に dml-derived-table を置くことで確実に空でない結果が返されるようにできます。
例：
- SELECT 'completed' as finished, (SELECT COUNT(*) FROM Products) as product_total
- FROM SYS.DUMMY LEFT OUTER JOIN
- ( UPDATE Products SET UnitPrice = UnitPrice * 1.07 )
- REFERENCING ( NONE ) ON 1=1
あるいは、より簡潔な方法として、セットオペレーション (
UNION, EXCEPT, or INTERSECT) の1つを使用したクエリ表現の一部として、以下も可能です。- SELECT 'completed' as finished, (SELECT COUNT(*) FROM Products) as product_total
- FROM SYS.DUMMY
- UNION ALL
- SELECT 'dummy', 1 /* This query specification will return the empty set */
- FROM ( UPDATE Products SET UnitPrice = UnitPrice * 1.07 )
- REFERENCING ( NONE )
===
SAP SQL Anywhere に関する詳細情報は、SAP SQL Anywhere Communityページ<英語> を参照してください。
上記のコミュニティーに掲載されている技術情報は、順次SQL Anywhere 日本語コミュニティ
に掲載しています。
SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。
SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。
Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
User Tagに「sql anywhere」「sql anywhere Japanese question」
を入力してください。
不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。
======================
ご購入に関するお問い合わせ
こちらよりお問い合わせください。
- SAP Managed Tags:
