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.
3条答案
按热度按时间uelo1irk1#
Please read the Microsoft's syntax conventions to better understand how to translate their documentation:
[ ] (brackets) Optional syntax item.
UPDATE :
4ioopgfo2#
Yes, the documentation is incomplete on this. Where it says:
It should say something like:
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.
jfewjypa3#
perform the update in the destination table, table t is a subquery table