In this post, originally written by Glenn Paulley and posted to sybase.com in January of 2010, Glenn continues his discussion on using SELECT over various DML queries, a feature that was added to SQL Anywhere in version 12
In a previous article, I presented some examples of how one can
SELECT rows from a dml-derived-table, a new SQL language feature of the SQL Anywhere 12 server. In this post, I want to briefly describe some other ways in which one can exploit dml-derived-tables to simplify applications.
The first thing to mention is that even though the title of this post is “SELECT over an UPDATE statement”, dml-derived-tables are derived tables and hence can be used in any context one might use a derived table – including, of course, update statements (
UPDATE). Consequently one can effectively “nest” one
UPDATE statement within another; here is an example using
MERGE in combination with
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)
In the above example, the table “modified_employees” models a collection of Employees whose state has been altered; the
MERGE statement above merges employee identifiers and names for those employees whose salary has been increased by 3% with those employees already extant in the “modified_employees” table.
Second, note the
OPTION clause on line 15 above. Option settings that are temporarily set using the
OPTION clause for this statement also apply to the nested
UPDATE statement in addition to the outermost
Third, since a dml-derived-table is – merely – just a derived table, multiple dml-derived-tablescan exist within the same SQL statement. Below is an example that combines independent updates of both the Products and SalesOrderItems tables in the Demo example database, and then produces a result based on a join that includes these modifications:
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
In cases where multiple dml-derived-tables exist, the order of execution of each update statement is implementation-defined and not guaranteed.
In a graphical plan, a dml-derived-table is indicated by a “DML” node in the access plan tree:
and the plan for the statement represented by the “DML” node are listed as subqueries:
Finally, the SQL Anywhere 12 GA release will feature support for embedding an update statement as a dml-derived-table, but without materializing its result. In this case, one uses the syntax
REFERENCING( NONE ) to signify that the results of the modified data are not directly available to outer blocks within the same SQL statement (although the
effect of such a statement may be). Hence the SQL grammar for a dml-derived-table is as follows:
<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), the result of the update statement is not materialized and hence is empty. Since the dml-derived-table is empty, one must carefully craft the nested statement to assure the intended result will be returned. The server internally uses
REFERENCING(NONE) for immediate materialized view maintenance. Within an application, one can ensure that a non-empty result will be returned by placing the dml-derived-table in the null-supplying side of an outer join, eg.
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
or, in a more straightforward way, as part of a query expression using one of the set operators (
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 )