求一段sql 批量更新的语句

xinshuaigexing 2013-02-27 03:52:25

数据库表 A

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

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

...全文

1365 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

szm341 2013-02-27

打赏
举报

引用 2 楼 szm341 的回复:

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

szm341 2013-02-27

打赏
举报

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

Mr_Nice 2013-02-27

打赏
举报

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

/*
col1	col2
0	4544
1	8
2	465
3	858
4	5555*/