Merge Delete on joined tables is allowed but has a bug

To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table. But they cannot be the target of a DELETE, even if columns from only one table are selected.

Msg 4405 Level 16 State 1
View or function 'x' is not updatable because the modification affects multiple base tables.

To get around this restriction, I attempted to use a MERGE against a dummy table. (Obviously this simplistic example could be written using a DELETE...WHERE EXISTS or by putting one table as the merge source, but the point stands. The original used ROW_NUMBER so these weren't possible.)

WITH Joined AS (
    SELECT t1.*
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=0
WHEN NOT MATCHED BY SOURCE THEN DELETE;

**dbfiddle** This was actually allowed. But what I found was that the table that was modified did not depend on the columns being selected, or their order. It depended purely on the **order** that the tables were **joined**. **This, to my mind seems completely buggy behaviour.** Experimenting with THEN UPDATE shows far more sensible behaviour: it depends on which columns are used in the THEN UPDATE clause, in the same way as a normal UPDATE statement. So, I think SQL Server should: * Either continue allowing updatable CTEs to be deleted from, but ensure that only one table's columns are selected (like an UPDATE), ensuring no ambiguity. * Or completely disallow THEN DELETE in MERGE when the source is an updatable CTE with multiple base tables. **Do I have some misunderstanding in how updatable views work, or is there an actual bug here?** ____ **A bug report has now been filed on Azure Feedback. Please vote for it here .**