This form of the update statement has special rules for running in this manner. They MUST be followed, or your data can be made wrong. See comments in code for more information.

DECLARE @Owned INT = 0,

@OnRent INT = 0,

@Sequence INT = 0,

@Category CHAR(3);

-- This form of the UPDATE statement has some particular rules.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

WITH SafetyCheck AS

(

SELECT Category,

Class,

Location,

RowNo,

Owned,

OnRent,

Sequence = ROW_NUMBER() OVER (ORDER BY Category,Class,Location,RowNo)

FROM dbo.TestTable

)

UPDATE t

SET @Sequence = CASE WHEN Sequence = @Sequence + 1 THEN @Sequence + 1

ELSE 1/0 END,

@Owned = Owned = IsNull(Owned, @Owned),

@OnRent = OnRent = ISNULL(OnRent, @OnRent),

@Category = Category -- ANCHOR COLUMN

FROM SafetyCheck t WITH (TABLOCKX)

OPTION (MAXDOP 1);

select * from dbo.TestTable;