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.

[MarcoBoeringa]

This Help page also specifically lists a restriction on using WHERE clauses to join data when using Versioned Views:

"Versioned views only work with an individual versioned table or feature class. You cannot use a where clause to join multiple tables together or restrict which rows or columns are included in a versioned view."

A slightly related issue is the other reported problem on the same page (see the "Note") of a potential issue with duplicate ObjectIDs with 1:N joins in ArcGIS for Desktop. I know you are only doing a 1:1 here and not using ArcMap, but still...:

"If you choose to replace views created by ArcGIS by altering the view afterward, you are taking responsibility for not only the query's correctness and efficiency but also its relevance and suitability for use with ArcGIS clients. For example, creating one-to-many views involving feature classes results in duplicate ObjectIDs. This leads to unexpected behavior in applications such as ArcMap and ArcCatalog, because the ObjectID is supposed to contain unique values."

[BrandonCales · Accepted solution]

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.

[RuchiraWelikala]

Hi Brandon,

I've run into this same issue and I'm wondering if you can explain your work around.

Essentially, I have to update the attributes within a feature class using SQL Server, however, like you, the data needed to update the feature class (the attributes only) come from another table that's joined one to one.

Any guidance would be appreciated.

Cheers

[MarcoBoeringa]

Good to hear you managed to find a working "work-around" and get to use the Versioned View for your purpose.

[RuchiraWelikala]

Further to my previous post, I posed this question on GIS Stackexachange and got another work around.

arcsde - Edit default version table view using SQL Server - Geographic Information Systems Stack Exc...

[RifkiFauzi]

I've tried updating field value using SQL merge, but still getting error:

Violation of PRIMARY KEY constraint 'a29_rowid_ix1'. Cannot insert duplicate key in object 'owner.a29'. The duplicate key value is (431430, 190).

anyone can help?