From the Archives: Select Over an Update Statement Part 3

[JasonHinsperger]

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 (INSERT, MERGE, DELETE, UPDATE). Consequently one can effectively "nest" one UPDATE statement within another; here is an example using MERGE in combination with UPDATE:

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 MERGE.

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

When using 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 (UNION, EXCEPT, or INTERSECT😞

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 )