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

Question

I want to update the bottom / last row in my table. I am trying to implement this solution, but nothing seems to be the 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'm still working on:

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

but this will not always work, as in my function some records are deleted, and I do not always have PeriodID in increments of 1.

Answer 1

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

Answer 2

There is not enough context in your question to give a bulletproof answer. Based on your working decision, how about instead of looking for an account, find max PeriodID? As long as the subsequent PeriodID is of greater importance, it must work to get the "last" record.

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

Answer 3

If you have a unique column (perhaps PeriodID?) In each row, you can 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 )