SQL Server 2005 多列 SUM 更新語法錯誤：子查詢缺少 SELECT

錯誤 SQL

UPDATE Tab_Checksum
SET 按時上課 = (SUM(startclass) FROM Tab_Check GROUP BY [name], checkdate), ...
-- 錯誤：SUM 子查詢缺少 SELECT 關鍵字

修正

每個標量子查詢必須以 SELECT 開頭：

UPDATE Tab_Checksum
SET 按時上課 = (SELECT SUM(startclass) FROM Tab_Check 
                 WHERE Tab_Check.[name] = Tab_Checksum.[name] 
                   AND Tab_Check.checkdate = Tab_Checksum.checkdate
                 GROUP BY [name], checkdate),
    按時下課 = (SELECT SUM(finishclass) FROM Tab_Check ...),
    -- 其他列同理
WHERE EXISTS (SELECT 1 FROM Tab_Check 
              WHERE Tab_Check.[name] = Tab_Checksum.[name] 
                AND Tab_Check.checkdate = Tab_Checksum.checkdate);

關鍵點

• 子查詢返回單值（標量），需關聯外部表主鍵（[name], checkdate）確保一行對一行更新。
• GROUP BY 在關聯條件下通常可省略（因已唯一），保留也無妨。
• 建議用 MERGE (SQL Server 2008+) 或 JOIN 更新性能更好：

UPDATE t1
SET t1.按時上課 = t2.上課和, t1.按時下課 = t2.下課和, ...
FROM Tab_Checksum t1
JOIN (
    SELECT [name], checkdate, 
           SUM(startclass) AS 上課和, SUM(finishclass) AS 下課和, ...
    FROM Tab_Check
    GROUP BY [name], checkdate
) t2 ON t1.[name] = t2.[name] AND t1.checkdate = t2.checkdate;

JOIN 更新避免多次掃描源表，效率顯著提升。