Common Table Expression

[dhascuba]

Does anyone have any example of using CTE with an UPDATE included?

[efnkay]

I'm not sure you can get there from here...The following cut/paste is from V5R4 release so I apologize if this capability has subsequently become available. It would be nice to use CTE column values in a positioned UPDATE...But I'm not sure you can unless it is now available?

The table name of a common table expression can only be referenced in the select-statement, INSERT statement, or CREATE VIEW statement that defines it. A common-table-expression can be used: In place of a view to avoid creating the view (when general use of the view is not required and positioned updates or deletes are not used.) When the desired result table is based on variables. When the same result table needs to be shared in a fullselect.

[opendave]

You might be able to get there if you flip your CTE from the top to the bottom, as an EXISTS clause instead of a CTE....

For example:
update
file1 f1
set
f1.status = 'O'
where exists
(select * from file2 f2 where f2.ordnum = f1.ordnum and f2.status = 'O')

Or if you are trying update fields in one file from the values in the CTE, you could embed SELECT(s) in your SET statements like this:
udpate
file1 f1
set
f1.status = (select f2.status from file2 f2 where f1.ordnum = f2.ordnum)

But back to your original question, I do not think it is possible to update/delete with a real CTE in the statement. If performance of either of the above two techniques is too poor, another alternative would be to use the statement you have as a CTE to create a table in QTEMP and then use that table in your embedded SELECT(s).