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’ )
SELECT * FROM @TmpTable