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.

How about firing a select with the same where clause? – samar Apr 08 '14 at 08:36 — samar, Apr 08 '14 at 08:36

score 16 · Accepted Answer · answered Apr 08 '14 at 08:37

16

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

But you can:

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

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

answered Apr 08 '14 at 08:37

marc_s

704,970
168
1,303
1,425

Great post & it helped me out a lot. It is odd that the Microsoft T-SQL "property" is named _Inserted_ though because an update actually runs. My point here is that I created a Stored Proc & I need the value of the updated value & then later (under maintenance) someone will see that i'm doing OUTPUT Inserted.[FieldName] & it's not readily apparent that this is actually the value which was updated. – raddevus Feb 07 '22 at 20:28
1

@raddevus: well - one *could* argue that an `UPDATE` really could be a `delete` on the old data (therefore, that's available in `Deleted`), and then an `insert` of the new data (thus that being available in `Inserted`). It's not done that way - but might be a mental model to understand what those pseudo tables are called .... – marc_s Feb 07 '22 at 20:43
That is very interesting. Thanks for making that point. You made me think more deeply about what is really going on. – raddevus Feb 07 '22 at 20:46

score 7 · Answer 2 · edited Feb 07 '22 at 18:55

7

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.

edited Feb 07 '22 at 18:55

raddevus

7,113
5
61
73

answered Apr 08 '14 at 08:36

Mike

1,440
1
14
30

1

Note that there is no comma before OUTPUT – Dany Gauthier May 16 '16 at 15:22

score 4 · Answer 3 · edited May 23 '17 at 11:54

4

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

edited May 23 '17 at 11:54

Community

1
1

answered Apr 08 '14 at 08:40

BAdmin

907
1
10
19

score -1 · Answer 4 · answered Apr 28 '16 at 22:45

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.

Get the Identity of Last Updated Row in SQL Server

4 Answers4

Linked