如何更新SQL Server中的top 1行
本文关键字:Server 中的 top 1行 SQL 何更新 更新 | 更新日期: 2023-09-27 18:03:33
我想更新表中的最新行。我可以使用
select top 1 ...
,但如何做到这一点与更新?
DECLARE @Result nvarchar(50)
SET @Result = (select top 1 ROUND(SUM(DATEDIFF(SS,CheckIn,CheckOut) / 60.0 / 60.0),2) from CheckInCheckOut)
update CheckInCheckOut
set CountHours= @Result
where UserId_Fk = 3
我想这样做:
update top 1 CheckInCheckOut
请举个例子。
编辑
我得到了正确的查询:
DECLARE @Result nvarchar(50)
SET @Result = (select top 1 ROUND(SUM(DATEDIFF(SS,CheckIn,'2014-05-15 03:30:32.507') / 60.0 / 60.0),2) from CheckInCheckOut where UserId_Fk=3 order by CheckInCheckOutId desc)
update CheckInCheckOut
set CheckOut='2014-05-15 03:30:32.507',CountHours=@Result
where CheckInCheckOutId = (select top (1) CheckInCheckOutId
from CheckInCheckOut
where UserId_Fk = 3
order by CheckInCheckOutId desc )
但是我得到的错误是
'Column "CheckInCheckOut.CheckInCheckOutId" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
如何在select语句中使用order by ?
试试这个:
UPDATE CheckInCheckOut
SET CountHours = @Result
WHERE UserId_Fk = (SELECT TOP 1 UserId_Fk
FROM CheckInCheckOut
ORDER BY [Your_Sort_Column])