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.

3
  • 1
    Adding a WHEN MATCHED clause is also sufficient to change the table affected by the WHEN NOT MATCHED BY SOURCE THEN DELETE clause (was wondering if the MERGE would end up modifying two different tables and this didn't happen) dbfiddle.uk/BL77Re5O Jan 18 at 20:17
  • Interesting, that also seems to give the more consistent result I mention (ie only one table's columns can be present in all UPDATE clauses), and the DELETE clause then works on the same table. So that is a workaround. Jan 18 at 23:04
  • 1
    @MartinSmith Posted here feedback.azure.com/d365community/idea/… Jan 22 at 11:26

1 Answer 1

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.

3
  • Docs for MERGE itself say "If target_table is a view, any actions against it must satisfy the conditions for updating views. For more information, see Modify Data Through a View." learn.microsoft.com/en-us/sql/t-sql/statements/… So I guess it does actually state that in the docs. Jan 19 at 11:13
  • Would have thought "any actions" include a DELETE, and "must satisfy" means that existing restrictions on those actions. But the docs are anyway contradictory: Updatable Views says "Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table." and nothing else, which implies that it's actually dependent on the columns the view is selecting, not the FROM. So the existing error for a normal DELETE contradict that. Jan 19 at 12:01
  • I agree the docs are not clear on it. Jan 19 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.