I am currently testing using the declare update statement below. This will update my StackCustomerAltID numericly from 1 thourgh 501. But I need to figure out how to add a where clause or a order by using my Customer column in asc order.
here is the where clause I need to add (where DelFlg = 0 and Customer <> ‘Spare’)
declare @Var int set @Var = 0 update StackCustomer set @Var = StackCustomerAltID = @Var + 1
Advertisement
Answer
Just don’t do this. The behaviour is undocumented and can give you very odd results. Use ROW_NUMBER
instead:
WITH CTE AS( SELECT StackCustomerAltID, ROW_NUMBER() OVER (ORDER BY Customer) AS RN FROM dbo.StackCustomer) UPDATE CTE SET StackCustomerAltID = RN;