0

I want to update latest row in the table. For that I can use 

select top 1 ...

but how to do this with update?

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

I want to do somewhat like this:

update top 1 CheckInCheckOut

Example would be appreciated.

EDIT

I got the correct query as :

  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 )

But I get this error as 

    '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.

'

How can I use order by in my select statement?

David
  • 15,894
  • 22
  • 55
  • 66
user3590485
  • 241
  • 1
  • 9
  • 19
  • 2
    Using a `TOP 1` without an explicit `ORDER BY` is pointless - which row is the `TOP 1` you're talking about? If you want `TOP 1`, you **MUST** define an order by specifying an `ORDER BY` – marc_s May 15 '14 at 09:19

1 Answers1

1

Try this:

UPDATE CheckInCheckOut
SET CountHours = @Result
WHERE UserId_Fk = (SELECT TOP 1 UserId_Fk 
                   FROM CheckInCheckOut 
                   ORDER BY [Your_Sort_Column])
Tanner
  • 22,205
  • 9
  • 65
  • 83