Updating field based on record count

[avantgd]

I am a novice to moderate SQL person in need of some expert level assistance.

I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort a sub-set of the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.

Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc

Due to project requirements, this is not a value that can be calculated as needed. It needs to be stored in the table.

All help is greatly appreciated.

 

[SQLSister]

Best I can think of is to put these records in a temp table or table variable with an identity column. The use the identity column value to update the table.

Questions about posting. See faq183-874