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 ;