CODEWITHSUNDEEP
sqldatabase
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10570

sql server 2008 r2 update max(recent) date in a table

I have this table

enter image description here

I want to update the max (recent) endDate for orderId = 12.

I tried like this

UPDATE Order_Status
    SET endTime = @startTime
    WHERE @orderID IN (
    SELECT OrderID
    FROM Order_Status
    GROUP BY @OrderID
    Having max (@startDate)

but this doesn't even complied, please what is the right? thanks

EDIT Sorry , I asked wrong, what I want is to update the end in the Most RECENT(starteTime)

I mean . what I want is to update the endTime value in the row which has the most recent StartTime

Upvotes: 0

Views: 96

Answers (1)

TechDo
TechDo

Reputation: 18629

Please try:

;with T as(
    select *, ROW_NUMBER() over (order by StartTime desc) RNum from Order_Status where OrderID=12
)
update top(1) T set endTime=@startTime

for multiple OrderID, try

;with T as(
select *, ROW_NUMBER() over (partition by OrderID order by StartTime desc) RNum from Order_Status
)
update T set endTime=@startTime where RNum=1

Upvotes: 1

Related Questions