Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Goal: I am trying to retrieve records that were affected by UPDATE query.
Based on IBMs article I can use the following query:
SELECT empno, salary FROM FINAL TABLE
(UPDATE employee SET salary = salary * 1.10 WHERE job = 'CLERK')
However when I try to run similar query in database I get the following error:
[42601][-199] [SQL0199] Keyword UPDATE not expected. Valid tokens: INSERT.
Looking at the Db2 for i 7.2 Knowledge Center, shows that only INSERT's are valid in data-change-table-references, not UPDATE's
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafztabref.htm
data-change-table-reference
|----+-FINAL-+--TABLE--(--INSERT statement--)--+--------------------+----|
'-NEW---' '-correlation-clause-'
If a data-change-table-reference is specified, the intermediate result table is the set of rows inserted by the INSERT statement.
The link you referenced in your original question is for DB2 for LUW (Linux, Unix and Windows) 9.7. This is essentially a different product than Db2 for i.
