Edit default version table view using SQL Server

Question

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.

BEGIN TRANSACTION
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]
COMMIT;

---

@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
WHEN MATCHED THEN
 UPDATE SET TargetTable.Status = SourceTable.Status_2
;

• Vince: I believe the Esri doc said you'd need to edit through a versioned view, which is a very different thing.
• MickyT: Are you able to do a straight update on the view, eg UPDATE dbo.MyTable_evw SET somecol = 'testvalue' WHERE someothercol = 'filtervalue'
• Ruchira Welikala: @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.
• Ruchira Welikala: @MickyT Yes, I am. That operation works perfectly. It's the join that seems to be issuing the error.
• MickyT: 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

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]
 )
WHERE EXISTS (
 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.

• Ruchira Welikala: 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
• MickyT: @RuchiraWelikala I'll edit in a second set statement