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

  1. Not only I never heard of the OUTPUT clause in UPDATE statements, but I also needed this in a bit of code I wrote two weeks ago. There, I had to get only a batch of rows from a table and process them, so I was creating a temporary table with TOP 10 rows, updated their status by joining with the temporary table, then outputing the table.

    Instead, with the help of OUTPUT, I can do: UPDATE TOP 10 … OUTPUT * INTO @tempTable Wicked!

  2. In this case (just one email update), you could use an old UPDATE syntax:
    DECLARE @Logins TABLE (
    UserID INT PRIMARY KEY,
    EmailAddress NVARCHAR(100) NOT NULL,
    Verified BIT NOT NULL DEFAULT 0,
    UNIQUE(EmailAddress)
    );

    INSERT @Logins (UserID, EmailAddress)
    VALUES (1, ‘a@b.c’), (2, ‘d@e.f’);

    DECLARE @UpdatedUserID INT;
    UPDATE @Logins
    SET Verified = 1,
    @UpdatedUserID = UserID
    WHERE EmailAddress = ‘a@b.c’;

    SELECT @UpdatedUserID AS [@UpdateduserID];

  3. Something worthy to remember is the Microsoft note for the OUTPUT clause: “An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.”

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

WP Like Button Plugin by Free WordPress Templates