My understanding is this is disallowed because UPDATE...FROM
has some quirky behaviours that are maintained due to backward compatibility. Making these work the same when the view has instead-of triggers would be difficult, perhaps impossible.
MERGE
has several weaknesses, but it does have well-defined and sane update semantics.
The primary example of this is MERGE
preventing ambiguous updates—where a target row is logically changed more than once by the SQL specification. If nothing in the schema absolutely guarantees a target row cannot be modified more than once, a MERGE
plan introduces operators to check this condition at runtime, and raise an error if it encountered.
This makes implementing joined-table MERGE
on a target with instead-of triggers easier/at all possible.
The UPDATE...FROM
syntax also has curious binding behaviours (again maintained to avoid breaking old code) when the target table is referenced more than once, sometimes by alias and sometimes not.
Not directly related to your question, but there is an example of an edge case with CTEs described in the documentation:
When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results.
All that said, just because it works with MERGE
doesn't mean there aren't bugs lurking. For a semi-related example see MERGE into a view with INSTEAD OF triggers.
Pushing hard at the edges of combinations of complex features is a great way to find edge-case bugs.