Update Versioned View with JOIN

BrandonCales

I'm told by support that this is a by-design issue with MS SQL "A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause."

There has to be workaround - anyone run across this? Essentially I am update my parcels attributes from data I received from another source. There are roughly 15 fields I update.

SQL:
UPDATE A SET A.APN11 = B.APN11
FROM XYZ.DBO.Q_PARCEL_VW A,ABC.DBO.Q_POD B
WHERE A.APN = B.APN
AND A.APN11 <> B.APN11

Error:
UPDATE is not allowed because the statement updates view "XYZ.DBO.Q_PARCEL_VW" which participates in a join and has an INSTEAD OF UPDATE trigger.

BrandonCales

I'm not editing anything that was designed/implemented from the ArcGIS Install/Setup/Versioning process. I definitely want to work with what's available and how it's designed.

I have however, found a work around that seems to work very well.


MERGE INTO XYZ.DBO.Q_PARCEL_VW AS T
USING (SELECT APN, APN11 FROM ABC.DBO.Q_POD) AS S
ON (T.APN = S.APN)
WHEN MATCHED AND (ISNULL(T.APN11,'') <> ISNULL(S.APN11,'')) THEN
UPDATE SET T.APN11 = S.APN11;

For non-numeric, I apply the ISNULL with '' as the replacement, otherwise I would use 0. The ISNULL was necessary I found out, because the MERGE doesn't like to run if the fields can have nulls - so you have to eliminate them.

So in SQL, I create a version, open an edit session, run my edits using MERGE, close the edit session; then in ArcCatalog I reconcile, post, and delete that version to the DEFAULT; then compress.

Everything seems to work great.