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 ); |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |