So you want to update a single row in your table and then return the row identifier that was affected, here’s one way of going about it. Remember SCOPE_IDENTITY() is about inserting so as we are updating here we need a little something different.

This will update a single record and output the id of the record it updated

DECLARE @TmpTable TABLE ( primary_id INT )

UPDATE dbo. table
SET someColumn= ‘Some value’
OUTPUT INSERTED. primary_id INTO @TmpTable
WHERE primary_id = (SELECT MIN( primary_id ) 
FROM dbo. table nolock WHERE someColumn <> ‘Some value’ )