字段循环增加内容

[rnon637]

我想给emBaseInf表中Mark字段为空的记录
修改为一个不唯一的值 0001到9999的值得
这个存储过程要怎么写呢？

[lzw_0736]

with a1 as ( select mark,row_number() over (order by @@servername) re from emBaseInf where Mark='' ) update a1 set mark=right('000'+rtrim(re),4)

[guguda2008]

1楼正解我蹭分

[LongRui888]

修改为一个不唯一的值，这样吗：

create table tb(ID  int,    mark varchar(100))


insert into tb
select 1,null union all
select 2,'a' union all
select 3,null union all
select 4,'b' union all
select 5,'b' union all
select 6,null 
go


declare @t int

set @t = 0

update tb
set @t = @t + 1,
    mark = case when mark is null then right('000'+cast(@t as varchar),4) 
                else mark end


select *
from tb
/*
ID	mark
1	0001
2	a
3	0003
4	b
5	b
6	0006
*/

[LongRui888]

修改了一下，如果达到了10000，也可以让数据在0001-9999之间：

--drop table tb

create table tb(ID  int,    mark varchar(100))

insert into tb
select 1,null union all
select 2,'a' union all
select 3,null union all
select 4,'b' union all
select 5,'b' union all
select 6,null 
go


declare @t int

set @t = 0

update tb
set @t = @t + 1,
    mark = case when mark is null then right('000'+cast(@t%10000 as varchar),4) 
                else mark end

select *
from tb
/*
ID	mark
1	0001
2	a
3	0003
4	b
5	b
6	0006
*/

[rnon637]

引用 1 楼 lzw_0736 的回复:

with a1 as ( select mark,row_number() over (order by @@servername) re from emBaseInf where Mark='' ) update a1 set mark=right('000'+rtrim(re),4)

如果第二次执行 还是从0000开始 怎么避免呢？

[lzw_0736]

begin tran
DECLARE @mark INT
SELECT @mark=MAX(MARK) from emBaseInf

;with a1 as
(
select mark,row_number() over (order by @@servername) re from emBaseInf where Mark=''
)
update a1 set mark=right('000'+rtrim(re+@mark),4)
commit tran