CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Angwenyi
Angwenyi

Reputation: 329

How to update a SQL Server table using the latest record

 | number |         date           | 
 +--------+------------------------+
 | p1     |2014-08-23 23:45:49.9902|
 | p1     |2014-08-23 23:46:32.033 |

I have a table in SQL Server as above with thousands of records, I would like to be able to update the latest record 

 update tableA  
 set number='p2'
 where date=????

Thanks in advance for the help

I used GETDATE() to save the records

Upvotes: 7

Views: 9337

Answers (2)

M.Ali
M.Ali

Reputation: 69494

If there can be multiple rows with the same Latest date value and you only want to update ONE row you can use the following 

;WITH CTE AS
 (
   SELECT TOP 1 number
   FROM tableA  
   ORDER BY [Date] DESC
 )
UPDATE CTE
 SET number = 'p2'

Upvotes: 3

juergen d
juergen d

Reputation: 204746

 update tableA  
 set number='p2'
 where date = (select max(date) from tableA)

Upvotes: 5

Related Questions