聚合不能出现在UPDATE语句的集合列表中.如果用列名更新

Question

运行时出现错误

聚合不能出现在UPDATE语句的set list中。

代码:

 update CheckInCheckOut 
 set CountHours = (select ROUND(SUM(DATEDIFF(ss,CheckIn,CheckOut) / 60.0 / 60.0), 2))  
 where UserId_Fk = 3

这个查询工作当我写它:

update CheckInCheckOut 
set CountHours = ROUND(SUM(DATEDIFF(ss, '2014-05-15 00:23:04.580', '2014-05-15 00:25:04.580')  / 60.0 / 60.0), 2)
   CheckIn datetime,
  CheckOut datetime

Answer 1

尝试声明一个变量:

DECLARE @Result Decimal(18, 0)
SET @Result = ROUND(SUM(DATEDIFF(ss,'2014-05-15 00:23:04.580','2014-05-15 00:25:04.580') / 60.0 / 60.0), 2)
update CheckInCheckOut 
set CountHours= @Result
where UserId_Fk=3