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:

• Updates all products in the sample database with a 7% price increase
• Lists the affected products and their orders that were shipped between April 10, 2000 and May 21, 2000 whose order quantity was greater than 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;

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

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;

 Using tables without materializing results

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;

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 );

 See also