Is it possible to return an existing column value before updating a row?

Question

I am writing a request that updates a user’s vote ( ForumVotes ) for a forum post ( ForumPosts ). Users can vote up or down (the vote will be 1 or -1). This question is specific for changing a user's vote, so a voice recording already exists in the ForumVotes table.

The ForumPosts table stores the total score for each post, so I need to synchronize this field. To recount the total score, I need to first subtract the old vote before adding a new vote, so I need to get the old vote before updating the user's voice recording.

I know I can do this with two queries, but I wonder if it is possible (in SQL Server 2008) that UPDATE returns the column value before performing the update?

Here is an example:

TABLE ForumPosts ( postID bigint, score int, ... etc ) -- existing vote is in this table: TABLE ForumVotes ( postFK bigint, userFK bigint, score int ) 

Simple request to update user voice

 UPDATE ForumVotes SET score = @newVote WHERE postFK = @postID AND userFK = @userID 

Can I change this request to return the old account before the upgrade?

Answer 1

Try the OUTPUT clause:

 declare @previous table(newscore int, Oldscore int, postFK int, userFK int) UPDATE ForumVotes SET score = @newVote OUTPUT inserted.score,deleted.score, deleted.postFK, deleted.userFK into @previous WHERE postFK = @postID AND userFK = @userID select * from @previous 

Answer 2

If this is a single row affected query (ie; update using key(s)) , then:

 declare @oldVote varchar(50) update ForumVotes set score = @newVote, @oldVote = score where postFK = @postId and userFK = @userId --to receive the old value select @oldVote 

Answer 3

I will tell you about @HLGEM's answer, showing that you do not need the @previous intermediate table, you can rely on OUTPUT , returning data directly without any variables, only smoothing:

 UPDATE ForumVotes SET Score = @newVote OUTPUT INSERTED.Score AS NewScore, DELETED.Score AS OldScore WHERE PostFK = @postId AND USerFK = @userId 

If you run this directly, you will get a table with 1 row of data and two columns: NewScore and OldSCore .