求一段sql 批量更新的语句
数据库表 A

列1 列2
0 4544
5 8
9 465
782 5555
548 858



求批量更新列1 的编号为 0 1 2 3 4 5 的SQL语句，保留0这一条。
[解决办法]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (col1 int,col2 int)
insert into [TB]
select 0,4544 union all
select 5,8 union all
select 9,465 union all
select 782,5555 union all
select 548,858

select * from [TB]

SELECT CASE WHEN col1 = 0 THEN 0 ELSE num-1 END AS col1,col2 FROM (
SELECT col1,ROW_NUMBER()OVER(ORDER BY col1 ASC) AS num,col2
FROM dbo.TB)T

/*
col1col2
04544
18
2465
3858
45555*/

[解决办法]

引用:

UPDATE tb SET col1=rn
FROM (
SELECT col1,ROW_NUMBER() OVER(ORDER BY col1) rn
FROM dbo.TB)a where tb.col1=a.col1

忘记-1了
UPDATE tb SET col1=rn-1
FROM (
SELECT col1,ROW_NUMBER() OVER(ORDER BY col1) rn
FROM dbo.TB)a where tb.col1=a.col1