I have a ArcSDE (10.3) geodatabase setup which is versioned as we have a few people working concurrently on the data. However, I want to run some background scripts in SQL Server to update the default version when no one is actually working on the data.

Anyway, the data I will be editing is actually a feature class. I'm not making any geometry changes, but just changing a few attribute values. However, this attribute change is based on a join to another SDE table residing inside the same database.

ESRI states that changes made from SQL Server must be made to the the table VIEW rather than the actual base table. However, when I execute my script, I'm given this error:

Msg 414, Level 16, State 1, Line 6
UPDATE is not allowed because the statement updates view "dbo.MyTable_evw" which participates in a join and has an INSTEAD OF UPDATE trigger.

How have other people have gotten around this issue?

Example of update statement that's throwing the above mentioned error.

EXEC set_default

UPDATE [dbo].[MyFeatureClass_evw]
SET [dbo].[MyFeatureClass_evw].[Status] = [dbo].[MySDEDataTable].[Status_2],

FROM [dbo].[MyFeatureClass_evw] INNER JOIN
[dbo].[MySDEDataTable] ON [dbo].[MyFeatureClass_evw].[PrimaryKey] = [dbo].[MySDEDataTable].[PrimeKey]


@MickyT's method works great. Also, it's worth mentioning that I also got the MERGE command to do this operation as well.

MERGE MyFeatureClass_evw as TargetTable
USING MySDEDataTable as SourceTable
ON TargetTable.PrimaryKey = SourceTable.PrimeKey
  UPDATE SET TargetTable.Status = SourceTable.Status_2
  • 2
    I believe the Esri doc said you'd need to edit through a versioned view, which is a very different thing.
    – Vince
    Jan 12, 2015 at 20:28
  • 1
    Are you able to do a straight update on the view, eg UPDATE dbo.MyTable_evw SET somecol = 'testvalue' WHERE someothercol = 'filtervalue'
    – MickyT
    Jan 12, 2015 at 21:03
  • @Vince I am editing the view, I believe. The table does have "evw" as a suffix as noted in the documentation, however, it throws the error when I run the update. When I run update commands without a join of any sort, the operation works fine and the changes are reflected immediately in the ArcMap/ArcGIS Server. Jan 12, 2015 at 21:04
  • @MickyT Yes, I am. That operation works perfectly. It's the join that seems to be issuing the error. Jan 12, 2015 at 21:05
  • 1
    I believe there may be a way to do this. Can you edit your question with an example of your update statement and I will see if I can modify it for you
    – MickyT
    Jan 12, 2015 at 21:06

1 Answer 1


This seems to have been a issue with SQL Server for some time now. Not sure why they don't allow it. If you change your update statement to something like this, it should work.

UPDATE [dbo].[MyFeatureClass_evw]
SET [dbo].[MyFeatureClass_evw].[Status] = (
    SELECT A.[Status_2] 
    FROM [dbo].[MySDEDataTable] AS A 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A.[PrimeKey]
    [dbo].[MyFeatureClass_evw].[Value] = (
    SELECT A.[Value_2] 
    FROM [dbo].[MySDEDataTable] AS A 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A.[PrimeKey]
    SELECT 1 
    FROM [dbo].[MySDEDataTable] AS A1 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A1.[PrimeKey]

It can get a bit wordy with the repetition of the SELECT queries for each column updated, but it works.

  • Wow, works perfectly! If I were to update more than one field, how would I go about amending your script? For instance, I want to bring over more than just the status field from the join table. Thanks for all your help, @MickyT Jan 12, 2015 at 21:41
  • 1
    @RuchiraWelikala I'll edit in a second set statement
    – MickyT
    Jan 12, 2015 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.