Getting the Id of a row I updated in Sql Server
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
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