Frequently I have to use queries of the following kind to update a local table based on values from a proxy table (usually the remote server being a MS SQL Server):
AFAIK, these queries are generally not supported and return a SQLCODE -728 ("Update operation attempted on non-updatable remote query").
My usual workaround is to fetch the needed remote values in a local temporary table and join that against the local table, something like
While that workaround is acceptable, is there a way in v12.0.1 to allow one-step updates with remote tables, i.e. some tuning of server capabilities or the like?
(Note: The udate/delete itself would always modify the local table, not the remote one - the latter often being used with a read-only remote server.)
I am sorry to tell you that the restriction with performing update/delete operations on a join between a proxy table and a local table is still in place for SA 12.0.1 and will likely remain in place for quite some time. I realize that the restriction only makes sense when it is actually the remote table that is being updated or deleted from; however, for consistency and other less obvious/internal reasons, the restriction is in place regardless of whether it is the local or remote table that is being modified. There are no tweaks you can make to capability bits or options that will allow the restriction to be relaxed. Your approach of using an intermediate temporary table is the correct/recommended approach.
You may be able to use the MERGE statement like:
answered 15 Apr '11, 15:34