Redtopia Redtopia - 2 years ago 68
SQL Question

SQL Server - Is it possible to return an existing column value before a row update?

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 (
postID bigint,
score int,
... etc

-- existing vote is in this table:

TABLE ForumVotes (
postFK bigint,
userFK bigint,
score int

A simple query to update a user's vote

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

Can this query be modified to return the old score before the update?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download