On 11-Apr-2012 10:57 , rob@xxxxxxxxx wrote:
6.1 and above are close with their "insert on from" but they don't
support "update on from".
CREATE TABLE orders( order_id INTEGER AS IDENTITY,
SELECT order_id FROM FINAL TABLE (
INSERT INTO orders VALUES(DEFAULT,'11/03/2007',50,'JM12'));
Interesting. I do not recall ever having heard those features
referred-to in that way, yet a web search yields plenty. I suppose the
term makes sense, after some thought, but upon first glance the "on"
seems amiss. An article I perused had described the composition of the
statement as having the "INSERT _in_ the FROM clause" of the SELECT, so
I somehow feel vindicated ;-)
What I thought was more typical to describe the capability, and to me
seems much more intuitively to describe them, are "select from insert"
[and "select from update" and "select from delete" and "select from
merge"]. From a quick web search, I believe all of those features are
available [except for merge] with the db2 v9 for z SQL; also, though
perhaps only v10, I saw a reference to "OLD TABLE" beyond just the "NEW
TABLE" and "FINAL TABLE" as data-change-table-reference for
table-reference. The "select from insert" seems the only, of those
capabilities, supported with the IBM i 7.1 SQL; from what I saw in the docs.
I suppose "transition tables" of statement-level triggers with the
OLD and NEW tables can give that functionality, access to the changed
data on an UPDATE, but I am not very familiar with those. For example,
how they might best expose that information. Global temporary tables
seem an obvious means. A quick test shows promise, albeit for the OP,
the simpler resolution would surely be to just FETCH the row data of