5

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;

db<>fiddle

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.

Charlieface
  • 17,078
  • 22
  • 44

1 Answers1

6

From one point of view, your question is unanswerable because it depends on the MERGE design intent. There's nothing in the documentation to address this case explicitly.

My opinion is that MERGE ought to behave the same as the non-MERGE option; that is to say the DELETE should be disallowed.

table_or_view_name
The name of the table or view from which the rows are to be removed.

A table variable, within its scope, also can be used as a table source in a DELETE statement.

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view definition.

(emphasis added)

The fact that MERGE doesn't produce an error here is probably because it is too hard for the parser to detect the condition. Or it's an oversight, as has been common with the exceptional complexity of MERGE variations.

In any case, my stance is you should avoid working around disallowed behaviours until you find something that 'works'. Or, you should not be surprised by any strange or non-deterministic results.

Report the issue as a bug to Microsoft so they can look into it.

Paul White
  • 94,921
  • 30
  • 437
  • 687