I'm writing a query that updates a user's vote (ForumVotes) for a forum post (ForumPosts). Users can vote up or down (the vote will equal 1 or -1). This question is specific to changing a user's vote, so a vote record already exists in the ForumVotes table.
The ForumPosts table stores the total score for each post, so I need to keep this field in synch. To recalculate the total score I need to first subtract the old vote before adding the new vote, so I need to get the old vote before updating the user's vote record.
I know I can do this with 2 queries, but I'm wondering if it's possible (in SQL Server 2008) for an UPDATE to return the value of a column prior to performing the update?
Here's an example:
TABLE ForumPosts (
-- existing vote is in this table:
TABLE ForumVotes (
SET score = @newVote
WHERE postFK = @postID
AND userFK = @userID
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