Using Select Case when with null values

I am trying to populate a column (2003_revenue) with the sum of two columns, STB and Addon that exist in the temp table #CombinedRevTable_2003. However, if both STB and Addon are null, I want to populate the column with Null.

我正在尝试使用临时表#combinedRevTable_2003中存在的两列STB和Addon的总和来填充列(2003_revenue)。但是,如果STB和Addon都为null,我想用Null填充列。

I keep getting an error with my code that there is incorrect syntax near the keyword 'from'.

我的代码一直出错,关键字'from'附近的语法不正确。

UPDATE data.revenuesummary
SET 2003_Revenue = (
    SELECT CASE 
      WHEN (STB IS NULL AND Addon IS NULL) THEN NULL 
      ELSE SUM(ISNULL(STB,0)) + SUM(ISNULL(Addon,0)) 
    FROM #CombinedRevTable_2003 b
    WHERE b.ID = data.revenuesummary.ID
)

Any assistance would be greatly appreciated.

任何帮助将不胜感激。

Thanks!

1 个解决方案

#1

You're missing an END for the CASE statement

您错过了CASE语句的END

UPDATE data.revenuesummary
SET 2003_Revenue = (
    SELECT CASE 
      WHEN (STB IS NULL AND Addon IS NULL) THEN NULL 
      ELSE SUM(ISNULL(STB,0)) + SUM(ISNULL(Addon,0))
    END
    FROM #CombinedRevTable_2003 b
    WHERE b.ID = data.revenuesummary.ID
)