Is being able to refer to a CTE in a FROM-less UPDATE clause undocumented behaviour?

Question

The syntax for the first part of UPDATE is documented as follows

UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object> | rowset_function_limited   
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
      }  
      | @table_variable      
    }  

and <object> is defined as

<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}  

Furthermore,

table_or_view_name

Is the name of the table or view from which the rows are to be updated. The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

To the best of my knowledge, none of the above refer to CTEs. And yet, putting a reference to a CTE immediately after UPDATE is totally valid. This answer shows this valid example

;WITH T AS
(   SELECT  InvoiceNumber, 
            DocTotal, 
            SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
    FROM    PEDI_InvoiceDetail
)
UPDATE  T
SET     DocTotal = NewDocTotal

Note that there is nothing between UPDATE and the reference to the CTE, not even a FROM.

Have I missed something in the documentation, or is being able to put a CTE immediately after UPDATE undocumented behaviour?

Note that I am not talking about the FROM clause in an update. I'm talking about UPDATE FOO where FOO is a CTE. There are examples of using CTEs in the documentation, but all of them use the FROM clause.

Answer 1

Please read the Microsoft's syntax conventions to better understand how to translate their documentation:

[ ] (brackets) Optional syntax item.

UPDATE:

-- Syntax for SQL Server and Azure SQL Database  

[ WITH <common_table_expression> [...n] ]  <--Optional per Microsoft
UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object> | rowset_function_limited   
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
      }  
      | @table_variable      
    }  
    SET  
        { column_name = { expression | DEFAULT | NULL }  
          | { udt_column_name.{ { property_name = expression  
                                | field_name = expression }  
                                | method_name ( argument [ ,...n ] )  
                              }  
          }  
          | column_name { .WRITE ( expression , @Offset , @Length ) }  
          | @variable = expression  
          | @variable = column = expression  
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
        } [ ,...n ]   
  
    [ <OUTPUT Clause> ]  
    [ FROM{ <table_source> } [ ,...n ] ] <--Optional per Microsoft
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                  { { [ GLOBAL ] cursor_name }   
                      | cursor_variable_name   
                  }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]  
[ ; ]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}  <--CTE is a result set as per the documentation for CTEs

Answer 2

perform the update in the destination table, table t is a subquery table

WITH T AS
(   SELECT  InvoiceNumber, 
            SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
    FROM    PEDI_InvoiceDetail
)
UPDATE  PEDI_InvoiceDetail 
SET     DocTotal = NewDocTotal
FROM            PEDI_InvoiceDetail INNER JOIN
                         T ON PEDI_InvoiceDetail.InvoiceNumber = T.InvoiceNumber

Answer 3

Yes, the documentation is incomplete on this. Where it says:

<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}  

It should say something like:

<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    writeable_table_expression}

This oversight in the doc has been around at least as long as CTEs have been in SQL Server, and this is not the only place that has this problem.