T-SQL How to update the bottom/last row only?

Question

I want to update the bottom/the last row in my table. I have try to implement this solution, but nothing seems as correct syntax:

UPDATE TOP(1) @ResultTable
SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)
ORDER BY PeriodID DESC

OR

UPDATE TOP(1) @ResultTable
SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)
FROM @ResultTable
ORDER BY PeriodID DESC

What I have till now working is:

UPDATE @ResultTable
SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)
WHERE PeriodID=(SELECT COUNT(PeriodID) FROM @ResultTable)-1

but this will not always works, as in my function some of the records are deleted and I am not always having PeriodIDs incremented with 1.

Solution 1

;WITH CTE AS 
( 
SELECT TOP 1 * 
FROM @ResultTable
ORDER BY PeriodID DESC
) 
UPDATE CTE SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)

Solution 2

There's not enough context in your question to give a bulletproof answer. Based on your working solution, how about instead of looking for the count look for the max PeriodID? As long as subsequent PeriodID's are a greater value it should work to get the "last" record.

UPDATE @ResultTable
SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)
WHERE PeriodID=(SELECT MAX(PeriodID) FROM @ResultTable)

Solution 3

If you have a unique column (perhaps PeriodID?) in each row you could do something like this:

UPDATE @ResultTable
SET PeriodLastDate=DATEADD(DAY,-1,PeriodLastDate)
where <unique column> = (select top 1 <unique column> 
  from @ResultTable
  order by PeriodID desc
  )

Solution 4

What about :

UPDATE ResultTable SET PeriodLastDate='NewValue' WHERE ID= (SELECT MAX(ID) FROM ResultTable)

View more solutions