Get Updated Row

Question

I have a query that updates one record, and only one record. Is there are way to get the Id updated in the same query such as Select ScopeIdentity when inserting.

UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
FROM (select top 1 table where SomeStuff)

Select Lastrow that just got updated.

Solution 1

Depending on what you are doing, you may need to use the table syntax of OUTPUT. A possibility is to specify a temporary table / table variable.

DECLARE @T TABLE
(
    MyID INT NOT NULL
)

UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
OUTPUT INSERTED.MyID INTO @T
FROM (/* your FROM clause here */) Task

gbn got an edit in ahead of me that essentially says the same thing as above. I would like to add that another way to do this is to grab the ID first and then update by ID. Also, TOP 1 should almost always be used with an ORDER BY.

-- You may need to clean up the error handling. I just wanted
-- to put something simple in to remind that it is necessary.
DECLARE @userid INT; SET @userid = /* e.g., */ 1234
BEGIN TRANSACTION
IF @@ERROR <> 0 RETURN
DECLARE @TaskID INT
SET @TaskID = (SELECT TOP 1 TaskID FROM Task WITH (UPDLOCK) ORDER BY /* e.g., */ TaskID) -- TaskID should be the PK of MyTable. Must be unique.
IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END
UPDATE Task
SET MyTime = GETDATE(), MyUserId = @userid
WHERE TaskID = @TaskID
COMMIT TRANSACTION

Solution 2

Yes, use the OUTPUT clause

Example:

UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
OUTPUT INSERTED.MyID
FROM (select top 1 table where SomeStuff)

or

DECLARE @MyTableVar TABLE (...

...
UPDATE Task
SET MyTime = GetDate(), MyUserId = @userid
OUTPUT INSERTED.MyID INTO @MyTableVar 
FROM (select top 1 table where SomeStuff)