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.

score 23 · Accepted Answer · answered Nov 08 '12 at 16:07

23

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

answered Nov 08 '12 at 16:07

Martin Smith

419,657
83
708
800

score 7 · Answer 2 · answered Nov 08 '12 at 16:08

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)

score 1 · Answer 3 · answered Nov 08 '12 at 16:09

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
  )

score 1 · Answer 4 · answered Jan 14 '21 at 22:08

1

What about :

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

answered Jan 14 '21 at 22:08

Laurent

156
2
10

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

4 Answers4