如何写这样的更新语句
有个字段的值
分别为
1
2
6
7
8
如何使用更新语句,将值改为
1
2
3
4
5
也就是值为记录的行数
[解决办法]
测试
create table test(a int,b varchar(10))
insert into test select 1, 'a '
insert into test select 3, 'b '
insert into test select 5, 'c '
select identity(int,1,1) id,b into #t from test
select * from #t
truncate原来的表，再把临时表数据回插到原来的表
[解决办法]
create table test(col int)
insert test select 1
union all select 2
union all select 6
union all select 7
union all select 8
select id=identity(int,1,1),* into # from test
update test set test.col=#.id from # where #.col=test.col
select * from test
drop table test,#
col
-----------
1
2
3
4
5
[解决办法]
如果是的话，可以这样：
update table
set column = (
select count(*)
from table
where column <= a.column )
from table a
[解决办法]
做一个临时表，结构和原表主键一样，增加一个自增字段。
将所有数据检索到这个临时表中。
然后用临时表的自增字段的值更新原表就可以了。
[解决办法]
create table test(
id int
)
insert into test
select 1 union
select 2 union
select 5 union
select 6 union
select 8
select * from test
update test
set test.id=(select count(*) from test as a where a.id <=b.id)
from test as b
select * from test
drop table test
[解决办法]
table col:
id,stackserialno,logoutflag
logoutflag 用来标识该条记录是否删除
table name tbltemp
update tbltemp set stackserialno = stackserialno - (select count(*) from tbltemp where logoutflag= '1 ')
where logoutflag= '0 ' and stackserialno > (select max(stackserialno) from tbltemp where logoutflag = '1 ')
