DECLARE @newOrderKey TABLE ( newKey int IDENTITY(1,1), oldKey int ) INSERT INTO @newOrderKey (oldKey) SELECT OrderKey FROM sometable ORDER BY OrderKey ASC UPDATE sometable SET OrderKey = (SELECT newKey FROM @newOrderKey nk WHERE nk.oldKey = sometable.OrderKey)OK, you can do it in one query but the performance will be horrible. It requires counting the distinct OldKey values prior the the current OldKey value:
UPDATE sometable SET OrderKey = (SELECT COUNT(DISTINCT OrderKey)+1 FROM sometable st2 WHERE st2.OrderKey < sometable.OrderKey)Be wary of this one. It's really going to depend on how sophisticated the query processor is. SQL Server should be able to handle it.