DML 文に対する SELECT

SQL Anywhere は、DML 文 (INSERT、UPDATE、DELETE、または MERGE) をクエリの FROM 句内のテーブル式として使用することをサポートします。

文に dml-derived-table を含めると、DESCRIBE では無視されます。OPEN 時には、UPDATE 文が最初に実行され、結果がテンポラリテーブルに格納されます。テンポラリテーブルでは、文によって変更されるテーブルのカラム名が使用されます。変更された値は、REFERENCING 句の相関名を使用して参照できます。OLD または FINAL を指定することによって、クエリで参照される更新されたテーブルに、一連のユニークなカラム名を指定する必要がなくなります。dml-derived-table 文は更新可能なテーブルを 1 つのみ参照できます。複数のテーブルに対する更新の場合は、エラーが返されます。

たとえば、次のクエリでは、UPDATE 文に対して SELECT を使用し、以下にリストする操作を実行します。

サンプルデータベース内のすべての製品の値段を 7% 上げるように更新する
影響を受ける製品とその注文のうち、2000 年 4 月 10 日から 2000 年 5 月 21 日までの間に出荷され、注文数が 36 より多いものをリストする

SELECT old_products.ID, old_products.name, old_products.UnitPrice AS OldPrice, 
       final_products.UnitPrice AS NewPrice, SOI.ID AS OrderID, SOI.Quantity
FROM
( UPDATE Products SET UnitPrice = UnitPrice * 1.07 )
     REFERENCING ( OLD AS old_products FINAL AS final_products )
  JOIN SalesOrderItems AS SOI ON SOI.ProductID = old_products.ID
WHERE SOI.ShipDate BETWEEN '2000-04-10' AND '2000-05-21'
      AND SOI.QUANTITY > 36
ORDER BY old_products.ID;

次のクエリは、MERGE 文と UPDATE 文の両方を使用しています。modified_employees テーブルは、ステータスが変更された従業員のコレクションを表しています。MERGE 文では、給与が 3% 上がった従業員と modified_employees テーブルに含まれている従業員の従業員 ID と名前をマージしています。このクエリで OPTION 句に指定されているオプション設定は、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 );

クエリでの複数テーブルの使用

クエリ内で複数の dml-derived-table 引数を使用する場合、UPDATE 文の実行順序は保証されません。次の文は、サンプルデータベースの Products テーブルと SalesOrderItems テーブルの両方を更新し、その操作を含むジョインに基づく結果を生成します。

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;

結果の実体化を伴わないテーブルの使用

REFERENCING ( NONE ) 句を使用すると、結果を実体化しないで UPDATE 文を埋め込むことができます。この場合は、UPDATE 文の結果が空となるため、意図した結果が返されるようにクエリを記述します。空ではない結果が返されるようにするには、外部ジョインの NULL 入力側に 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、または INTERSECT) のいずれかを使用するクエリ式の一部として dml-derived-table を使用することによって、空ではない結果が返されるようにすることもできます。次に例を示します。

SELECT 'completed' AS finished, ( SELECT COUNT( * ) FROM Products ) AS product_total
FROM SYS.DUMMY
UNION ALL
SELECT 'dummy', 1 /* This query specification returns the empty set */
FROM ( UPDATE Products SET UnitPrice = UnitPrice * 1.07 ) 
      REFERENCING ( NONE );

参照

DocCommentXchange で意見交換できます