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.
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!
You can always go back and refactor it or, hopefully not, refucktor it 😆
What can I say, better late than never!
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];
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.”