views:

52

answers:

3

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.
A: 

I don't know if SQL Server offers that functionality per se, but if I interpret that correctly, it would be the Task with the latest MyTime. So it is easy to identify it.

StampedeXV
Maybe not, say it if it's smalldatetime... there could be 100s in the minute interval
gbn
+2  A: 

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)
gbn
Really Great Answer!
+1  A: 

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
binarycoder
Thanks for the great answer! There is an order by I just did not include it:)
Why do you need UPDLOCK? We are already having deadlock issues so will this not increase using UPDLOCK
UPDLOCK: In this case, not necessary if you are using REPEATABLE READ or SERIALIZABLE isolation level. Otherwise, it ensures that another user cannot jump in and update (or even delete!) the row in the short time period between the SELECT and the UPDATE statement. That might not matter in your case, but I think it is a good practice to indicate you are doing a "SELECT with intent to UPDATE". Often this will actually decrease deadlocks, but it can depend case by case.
binarycoder