如何在带DECLARE的UPDATE语句中添加WHERE子句及ORDER BY排序规则

最终适配后的SQL语句 #

你要求的添加了WHERE筛选条件、且支持按Customer字段升序排序后更新的完整语句如下：

DECLARE @Var int
SET @Var = 0

UPDATE StackCustomer
SET @Var = StackCustomerAltID = @Var + 1
WHERE DelFlg = 0 AND Customer <> 'Spare'
ORDER BY Customer ASC

兼容写法（适配不支持UPDATE直接加ORDER BY的SQL Server版本） #

如果上述语句执行时报错不支持UPDATE后接ORDER BY，可以使用可更新CTE的方式实现，效果完全一致：

DECLARE @Var int
SET @Var = 0

WITH FilteredSortedCust AS (
    SELECT TOP 100 PERCENT StackCustomerAltID
    FROM StackCustomer
    WHERE DelFlg = 0 AND Customer <> 'Spare'
    ORDER BY Customer ASC
)
UPDATE FilteredSortedCust
SET @Var = StackCustomerAltID = @Var + 1

注意事项 #

• 执行更新前建议先运行查询校验符合条件的数据行数，避免误操作：

  SELECT COUNT(1) FROM StackCustomer WHERE DelFlg = 0 AND Customer <> 'Spare'
  

• 两种写法都会先筛选符合条件的行，再按Customer字段升序排列后，给StackCustomerAltID从1开始连续赋值

内容的提问来源于stack exchange，提问作者JShaffer