How to add a where clause and / or a order by to a update statement with declare-CodePudding

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

CodePudding user response：

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;