SQL Update语句，需要帮助才能将值传递给子查询

Question

这是我的SQL语句。它不起作用。

UPDATE dbo.Smoothie
SET TotalCalories = (SELECT 
                        SUM(CASE WHEN (Unit = 2) THEN f.Energ_Kcal * f.GmWt_2 * si.Quantity / 100 
                                 ELSE f.Energ_Kcal * f.GmWt_1 * si.Quantity / 100 
                            END) AS calories
                     FROM dbo.SmoothieIngredients AS si
                     INNER JOIN dbo.FoodAbbrev AS f ON si.FoodId = f.Id
                     WHERE si.SmoothieId = SmoothieId  ---> i want to pass the SmoothieId from the main update statement to the subquery.
                    )

我试着给它取个名字S2，但还是没用。

UPDATE dbo.Smoothie as S2
SET S2.TotalCalories = (SELECT 
                           SUM(CASE WHEN (Unit = 2) THEN f.Energ_Kcal * f.GmWt_2 * si.Quantity / 100 
                                    ELSE f.Energ_Kcal * f.GmWt_1 * si.Quantity / 100 
                                END) AS calories
                        FROM dbo.SmoothieIngredients AS si
                        INNER JOIN dbo.FoodAbbrev AS f ON si.FoodId = f.Id
                        WHERE si.SmoothieId = S2.SmoothieId)

Answer 1

您需要使用查询创建一个新表，然后将TotalCalories的值设置为列中的值。

所以在这种情况下：

UPDATE  dbo.Smoothie
SET     TotalCalories = s2.calories
FROM 
    ( SELECT    
         smoothieId,
         SUM(CASE WHEN (Unit = 2) THEN f.Energ_Kcal * f.GmWt_2 * si.Quantity / 100 
                                            ELSE f.Energ_Kcal * f.GmWt_1 * si.Quantity / 100 END) AS calories
         FROM      dbo.SmoothieIngredients AS si
         INNER JOIN dbo.FoodAbbrev AS f ON si.FoodId = f.Id
         GROUP BY SmoothieId
    ) AS s2
WHERE dbo.Smoothie.Id = s2.smoothieId

我的查询可能有点错误，但请注意s2表的查询中的GroupBy，然后像往常一样用Where子句链接行。