Getting the Id of a row I updated in Sql Server

Question

I'm trying to return the Id of a row I update in sql

UPDATE ITS2_UserNames
  SET AupIp = @AupIp
  WHERE @Customer_ID = TCID AND @Handle_ID = ID

  SELECT @@ERROR AS Error, @@ROWCOUNT AS RowsAffected, SCOPE_IDENTITY() AS ID

and I keep getting Null for the ID, how can I get this?

score 65 · Accepted Answer · edited Oct 24 '18 at 17:55

65

The @@identity and scope_identity() will hand you the identity of a new row, ie. after an insert. After your update, the identity of the row is... @Customer_ID or @Handle_Id? If it is a different field, you should use the OUTPUT clause to return the ID of the updated row:

UPDATE ITS2_UserNames  
SET AupIp = @AupIp  
OUTPUT INSERTED.PrimaryKeyID
WHERE @Customer_ID = TCID AND @Handle_ID = ID

edited Oct 24 '18 at 17:55

elyor

998
9
20

answered Oct 22 '09 at 23:10

Remus Rusanu

288,378
40
442
569

I can't find any evidence that this worked on SQL Server 2000... not from my memory, not from any blog posts, and not from official documentation. In fact there are several places in SQL Server 2005 Books Online where they tout "the new OUTPUT clause." When I try the above in 2000, I get a syntax error, as I would expect. What leads you to believe this works in SQL Server 2000? May be a moot point anyway. Wish there was a way to force questions to include version information. – Aaron Bertrand Oct 23 '09 at 00:54
@Aaron: Code was talking about the use of SCOPE_IDENTITY(), sry for confusion, I deleted one of my own comments and this disrupted the meaning. – Remus Rusanu Oct 23 '09 at 01:06
1

Gotcha, I spent half an hour trying to figure out why everyone suddenly thought OUTPUT was supported in 2000. I was only 99.99999% sure it wasn't. :-) – Aaron Bertrand Oct 23 '09 at 01:23
Great answer! And if you want to get the output into a variable just declare a table variable and use the INTO keyword. Like this: DECLARE (at)myTableVar TABLE(id INT) UPDATE ... SET ... OUTPUT inserted.PrimaryKeyID INTO (at)myTableVar WHERE ... (I couldn't write the @ for the variables so substitute (a)) – Björn Sep 18 '13 at 13:16
`@@identity` and `scope_identy()` only works if datatype of primary key is integer. If you use `uniqueidentifier` datatype then we must use `OUTPUT INSERTED` – Bhavik Jani Dec 21 '15 at 05:40
@@identity will return the wrong value if you use a trigger or something jumps in-between your insert and querying @@identity. use scope_identity() if it is supported. – TamusJRoyce Mar 22 '18 at 19:27

score 8 · Answer 2 · answered Mar 16 '19 at 22:30

I think what @pauloya tried to say is:

if you will update a table then you have a WHERE clause, so if you use that same where clause on a select with an INTO #tempTable you have all rows affected by your UPDATE.

So you can go:

SELECT
    userName.ID
INTO #temp
FROM ITS2_UserNames AS userNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID

then you update

UPDATE ITS2_UserNames
SET AupIp = @AupIp
WHERE @Customer_ID = TCID AND @Handle_ID = ID

finally you can return all IDs affected by your update

SELECT * FROM #temp

You can do this with OUTPUT but you will have to declare a variable table like

DECLARE @tempTable TABLE ( ID INT );

and then you use OUTPUT

UPDATE ITS2_UserNames  
SET AupIp = @AupIp  
OUTPUT INSERTED.ID
INTO @tempTable
WHERE @Customer_ID = TCID AND @Handle_ID = ID

score 3 · Answer 3 · answered Oct 08 '19 at 16:40

Wanted to mention here that if you want to take affected row's primary key in variable then you can use OUTPUT clause which can put this in a table variable. Execute below statements for example...

CREATE TABLE ItemTable(ID INT IDENTITY(1,1),Model varchar(500) NULL, Color VARCHAR(50) null)

INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Yellow')

INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Red')

INSERT INTO ItemTable(Model, Color) VALUES('SomeModel', 'Pink')

DECLARE @LastUpdateID TABLE(ItemID INT null)

UPDATE ItemTable SET model = 'abc' OUTPUT INSERTED.ID INTO @LastUpdateID WHERE Color = 'Red'

SELECT ItemID FROM @LastUpdateID

pauloya · Answer 4 · 2009-10-22T23:27:06.280

-2

If you want to find out the records that match that update you should do a select with it

Select IdColumn
From ITS2_UserNames
WHERE @Customer_ID = TCID AND @Handle_ID = ID

edited Oct 22 '09 at 23:27

answered Oct 22 '09 at 23:12

pauloya

2,535
3
30
50

1

This won't work if you're updating a column ur also selecting on – Talon Feb 15 '18 at 07:08

Getting the Id of a row I updated in Sql Server

4 Answers4

Linked