Get Updated Row

sql, sql-server, sql-server-2005, t-sql

Solution

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

Problem

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. ```

Original source