'Get the Identity of Last Updated Row in SQL Server

@@IDENTITY returns the ID of the last row inserted, I want to retrieve the ID of the last row updated.

Here is my query:

UPDATE [Table] 
SET Active = 1, 
    Subscribed = 1, 
    RenewDate = GETDATE(),
    EndDate = DATEADD(mm,1,getdate()),
WHERE SC = @SC
  AND Service = @Ser

How do I get the ID of this updated row?

The column is called TableID and I'm not using it in the query.

sql-serverstored-proceduressql-update


Solution 1:[1]

you can try using this:

OUTPUT INSERTED.TableID

in your code it would look like this:

    UPDATE [Table] 
    SET Active = 1, 
        Subscribed = 1, 
        RenewDate = GETDATE(),
        EndDate = DATEADD(mm,1,getdate())
OUTPUT INSERTED.TableID 
    WHERE SC = @SC
      AND Service = @Ser

Hope this helps.

Solution 2:[2]

I guess you need this one,

UPDATE [Table] 
SET Active = 1, 
    Subscribed = 1, 
    RenewDate = GETDATE(),
    EndDate = DATEADD(mm,1,getdate())
    OUTPUT INSERTED.TABLE_PrimaryKeyID
WHERE SC = @SC
AND Service = @Ser

Main source: here

Solution 3:[3]

Try using select @@identity gives last updated identity for the particular session (or) select scope_identity gives last updated identity for the particular scope (or) select ident_curr('tablename') give the last updated identity regardless of the session or scope but for that particular table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 raddevus
Solution 2 Community
Solution 3 sneha

Related Questions