Auto Increment Number in a single UPDATE statement

SQL Server 2005/2008

Suppose in a table, you want to increase the value of a Field (not identity field), you can write a SQL statement as following:

Update yourTable  set  yourField = max(yourField)+1  where .....

But, if the WHERE clause returns more than one records, then what you get is, for that more than one records, all the yourField will be update with the same value. If you want the yourField all has different value, you can write something like this:

declare @max int

select @max= MAX(yourField)  from yourTable

update yourTable set @max=yourField=@max+1 where ........