Skip to content
Advertisement

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

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement