How to get the ID of last updated Row in SQL Server 2008

Question

I have a requirement such that I want to get the id of last updated row in a table and I want to use this id for some other operation.

I don't have column like UpdatedOn , so that I can refer that column.

So is there any function like scope_identity and @@identity(which gives me id of last inserted row id) for Update also.

Please help me out for the same.

Solution 1

You can do something like this

declare @mytable as TABLE
    (
      Id int
    )

Update Table Set Name='Nitin'
OUTPUT INSERTED.Id into @mytable
 where LastName='Varpe'

 Select Id from @mytable

Solution 2

Since you're only interested in the last ID updated, then you could simply assign it to a variable in the SET clause:

DECLARE @LastID INT = NULL

UPDATE 
    SomeTable
SET
    SomeColumn = 'SomeValue',
    @LastID = ID
WHERE
    SomeOtherColumn = 'SomeOtherValue'

Comments

[Ronen Festinger]

Great solution, I would have never thought you can set variables with the updated row values this way. Thanks.