Re: SQL Update value in RPG?

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,
order_date DATE,
order_qty INTEGER,
order_item CHAR(4));

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

Regards, Chuck