OUTPUT Clause in UPDATE Statements

Sometimes, you need to update data in a table and then update data in another table but based on a filter condition found from the first table. Specifically, have you had to do this in the past?

-- ...

UPDATE Users
SET    Verified     = 1
FROM   Logins
WHERE  EmailAddress = @EmailAddress

DECLARE @UserId INT;

SELECT TOP 1
       @UserId = UserId
FROM   Logins
WHERE  EmailAddress = @EmailAddress

UPDATE  Users
SET     State = 2 -- Verified
WHERE   Id = @UserId

-- ...

This is not only inefficient (from an execution plan perspective) but also prone to race conditions and requires more code. The simpler and safer alternative is to use the OUTPUT clause of the UPDATE.

Here’s how:

DECLARE @UserIdTable TABLE ( Id INT );

UPDATE Users
SET    Verified     = 1
OUTPUT UserId
INTO   @UserIdTable
FROM   Logins
WHERE  EmailAddress = @EmailAddress

DECLARE @UserId INT = SELECT TOP 1 Id FROM @UserIdTable;

UPDATE  Users
SET     State = 2 -- Verified
WHERE   Id = @UserId

In the above code sample, I take advantage of the new declare and initialize syntax introduced in SQL Server 2008. The OUTPUT clause has been introduced in SQL Server 2005 so nothing here is really news.

Another simplification that I hoped was possible was to avoid the declaration of the local table variable and just push the OUTPUT into the local variable (@UserId) but it seems you can’t.

I found out about the OUTPUT clause recently from Remus Rusanu’s blog post about implementing queues with tables. These have, usually, high concurrency and any race condition that might occur will occur. OUTPUT is usually the best way to solve it.