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

order_date DATE,
order_qty INTEGER,
order_item CHAR(4));

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

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].