Get the Identity of Last Updated Row in SQL Server

Question

@@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.

Answer 1

You cannot retrieve an ID since there is no ID being inserted.....

But you can:

1. just query the table using the same criteria as in your UPDATE:

   SELECT TableID 
   FROM dbo.Table
   WHERE SC = @SC AND Service = @Ser  -- just use the same criteria
   

2. use the OUTPUT clause on the UPDATE to get that info:

   UPDATE [Table] 
   SET Active = 1, 
       Subscribed = 1, 
       RenewDate = GETDATE(),
       EndDate = DATEADD(mm,1,getdate())
   OUTPUT Inserted.TableId       -- output the TableID from the table
   WHERE SC = @SC AND Service = @Ser
   

Read more about the OUTPUT clause on Technet - it can be used on INSERT and DELETE as well

Answer 2

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.