如何从CTE中获得的值更新?-SQL Server

Question

我正在尝试根据CTE的值更新表中的数据。。我遇到了几个问题，目前SQL Server说它找不到列名WeightedRating，我真的不知道为什么。。或者它在说什么。

目前，我正在尝试在CTE中混合一个MERGE关键字。

这是我的完整查询：

DECLARE @COUNT_VALUE FLOAT  -- MINIMUM OF VOTES REQUIRED TO BE LISTED IN THE TOP
DECLARE @minimumVotesRequired FLOAT  -- MINIMUM OF VOTES REQUIRED TO BE LISTED IN THE TOP
SET @minimumVotesRequired = 3
;WITH CTE_2 (SumOfVoteScore,CountOfVotes,IdProduct)
AS
(
SELECT
        SUM(r.Stars) AS SumOfVoteScore, -- THIS IS THE SUMMATORY OF ALL THE STARS THAT WERE GIVEN TO THE PRODUCT
        COUNT(rap.IdProduct) AS CountOfVotes, --HOW MANY RATINGS OF PRODUCTS WHERE MADE, this is the vote count
        rap.IdProduct
    FROM
        glamstapp.Rating AS r INNER JOIN glamstapp.RatingAndProducts AS rap ON r.IdRating = rap.IdRating
    GROUP BY
        rap.IdProduct
)
,CTE_3 (idProduct,vote_count,vote_mean1,vote_mean2)
AS
(
        SELECT  CONVERT(FLOAT,rap.IdProduct) as IdProduct,
               --ISNULL(CTE_2.SumOfVoteScore,0) AS vote_sum,
               CONVERT(FLOAT,CTE_2.CountOfVotes) AS vote_count,
               COALESCE((CONVERT(FLOAT,CTE_2.SumOfVoteScore)/  (CONVERT(FLOAT,CTE_2.CountOfVotes))),0) AS vote_mean1, --THE MEAN OF EACH PRODUCT
               COALESCE((CONVERT(FLOAT,CTE_2.SumOfVoteScore)/  (CONVERT(FLOAT,CTE_2.CountOfVotes))),0) AS vote_mean2
        FROM glamstapp.RatingAndProducts AS rap INNER JOIN  CTE_2 ON rap.idProduct = CTE_2.idProduct  
        GROUP BY rap.IdProduct,
                 CTE_2.SumOfVoteScore,
                 CTE_2.CountOfVotes
)
MERGE INTO glamstapp.RatingAndProducts
USING
(
SELECT CTE_3.idProduct,(CONVERT(FLOAT,CTE_3.vote_count) / (CONVERT(FLOAT,CTE_3.vote_count) + @minimumVotesRequired))
                        * CONVERT(FLOAT,CTE_3.vote_mean1) + 
                        (@minimumVotesRequired / (CONVERT(FLOAT,CTE_3.vote_count)+ @minimumVotesRequired))
                        * (SUM(CONVERT(FLOAT,CTE_3.vote_mean2))/(select count (CTE_3.vote_mean2) from CTE_3)) AS WeightedRating
FROM CTE_3 
GROUP BY
         CTE_3.vote_count,
         CTE_3.IdProduct,    
         CTE_3.vote_mean1   
)
AS Source
ON glamstapp.RatingAndProducts.idProduct = source.idProduct
WHEN MATCHED THEN
UPDATE SET glamstapp.RatingAndProducts.WeightedRating =  //<--The error is present in here or so SQL Server points out when I double click on the error.
source.WeightedRating ;;

Answer 1

将glamstapp.RatingAndProducts.WeightedRating更改为仅WeightedRating。您不需要通过数据库名称或表来限定名称，因为merge语句只能影响一个已经明确声明的表，事实上，我认为在UPDATE和merge语句中，甚至两到四个部分的名称都是被禁止的。语法请参阅MERGE（Transact-SQL），相关部分为：

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

那么<merge_matched>的定义是什么呢？

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

好吧，那么什么是<set_clause>？

<set_clause>::=
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 ] 

需要注意的重要一点是，语法结构使用column_name而不是<column_name>，后者可以用多部分名称指定：

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

同样，这里的总结和关键是，当使用MERGE语句时，目标表已经明确指定，因此您不需要在SET表达式的左侧使用多部分名称，而且语法实际上禁止这样做，因为只有一个有效的表名，一个有效数据库名，SET表达式的每个部分的左手侧的一个有效服务器名称。