Noob question here, every time I change a certain record in an SQL Server 2008 R2 table, I want to increment a RevisionId record; to do so, I'm using the following syntax:
UPDATE TheTable
SET RevisionId=(SELECT RevisionId
FROM TheTable
WHERE Id=@id) + 1
WHERE Id=@id;
Btw, I'm going to put this into a trigger so that this happens automagically, but while this code works, it feels pretty clunky—any cleaner way to do this?
You don't need the inner select:
UPDATE TheTable SET RevisionId = RevisionId + 1 WHERE Id=@id
This is a SQL idiom for incrementing a field:
UPDATE TheTable
SET RevisionId = RevisionId + 1
WHERE Id=@id;
If you're just looking for a "row version" value, have you considered adding a TimeStamp column to your table? SQL Server updates it for you "automagically" every time. No code on your part at all. They just won't be sequentially numbered, if that's important to you.
