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):

:::SQL
update LocalTable LT
set col = PT.col
from LocalTable LT inner join ProxyTable PT on LT.pk = PT.pk;

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

:::SQL
select pk, col into local temporary table TempTable
from ProxyTable
order by pk;

update LocalTable LT
set col = TT.col
from LocalTable LT inner join TempTable TT on LT.pk = TT.pk;

drop table TempTable;

Question:

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

asked 15 Apr '11, 06:30

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 15 Apr '11, 06:32


Volker,

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.

permanent link

answered 18 Apr '11, 07:47

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 18 Apr '11, 09:03

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274

Thanks for the clarification - I can easily cope with that limitation:)

(18 Apr '11, 07:53) Volker Barth

You may be able to use the MERGE statement like:

:::SQL
MERGE INTO LocalTable USING ProxyTable ON LocalTable.pk = ProxyTable.pk WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN SKIP;

http://dcx.sybase.com/index.html#1201/en/dbreference/merge-statement.html

permanent link

answered 15 Apr '11, 15:34

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

I have been very existing about this approach - MERGE is one of the newer statements I don't use too often.

However, unfortunately, this approach seems to fail, too, with a -134 ("MERGE INTO <remote_table> is not supported") error message (tested on Win32 with 12.0.1.3298). Note that - in contrast to the error message - I'm trying to merge into the LocalTable as in Tyson's sample.

As I understand, the particular branches of a MERGE statement's actions are done with ordinary INSERT/UPDATE/DELETE statements - therefore it seems reasonable that MERGE has the same restrictions as the underlying UPDATE statement.

(18 Apr '11, 04:07) Volker Barth
Replies hidden

Should read "I have been very excited at this approach..."

(18 Apr '11, 07:22) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×413
×63
×47
×31

question asked: 15 Apr '11, 06:30

question was seen: 2,033 times

last updated: 18 Apr '11, 09:03