[GIS] Edit default version table view using SQL Server

arcgis-10.3editingenterprise-geodatabasesql serverversioning

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
;

Best Answer

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.

Related Question