Executing a SELECT over a DML statement

SQL Anywhere supports the use of a DML statement (INSERT, UPDATE, DELETE, or MERGE) as a table expression in a query's FROM clause.

When you include a dml-derived-table in a statement, it is ignored during the DESCRIBE. At OPEN time, the UPDATE statement is executed first, and the results are stored in a temporary table. The temporary table uses the column names of the table that is being modified by the statement. You can refer to the modified values by using the correlation name from the REFERENCING clause. By specifying OLD or FINAL, you do not need a set of unique column names for the updated table that is referenced in the query. The dml-derived-table statement can only reference one updatable table; updates over multiple tables return an error.

For example, the following query uses a SELECT over an UPDATE statement to perform the operations listed below:

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_prodcuts.ID;

The following query uses both a MERGE statement and an UPDATE statement. The modified_employees table represents a collection of employees whose state has been altered, while the MERGE statement merges employee identifiers and names for those employees whose salary has been increased by 3% with employees who are included in the modified_employees table. In this query, the option settings that are specified in the OPTION clause apply to both the UPDATE and MERGE statements.



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 );
 Using multiple tables within a query
 Using tables without materializing results
 See also