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

f0brbegy 于 12个月前发布在其他

关注(0)|答案(3)|浏览(77)

The syntax for the first part of UPDATEis 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.

sql-server

来源：https://stackoverflow.com/questions/77223643/is-being-able-to-refer-to-a-cte-in-a-from-less-update-clause-undocumented-behavi

3条答案

按热度按时间

uelo1irk1#

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

赞(0）回复(0）举报 12个月前

4ioopgfo2#

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.

赞(0）回复(0）举报 12个月前

jfewjypa3#

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

赞(0）回复(0）举报 12个月前

我来回答

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

3条答案

相关问题

热门标签

最新问答