Re: SQL Update value in RPG?

How using an update cursor?

DECLARE C1 CURSOR FOR
SELECT COUNTER
FROM FILE
WHERE KEY1 = :H_KEY1
AND KEY2 = :H_KEY2
FOR UPDATE;

OPEN C1;
FETCH C1 INTO :H_COUNTER; // READ AND LOCK THE RECORD.
H_COUNTER += 1;

UPDATE FILE SET COUNTER = :H_COUNTER
WHERE CURRENT OF C1;

CLOSE C1;


but if you go that route might as well do a chain with lock, increase the
variable, update and move one.



On Fri, Apr 13, 2012 at 1:32 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.