假设主键字段为f1,最多12位Select identity(int,1,1) as id,f1 into #tmp from 表 update 表 set f1 = left(rtrim(b.f1)+ cast(b.id as varchar(10)),12) from #tmp b where b.f1 = 表.f1
假设主键字段为f1,最多12位Select identity(int,1,1) as id,f1 into #tmp from 表 update 表 set f1 = 'dsp' + right('000000000'+ltrim(rtrim(cast(b.id as varchar(10)))),9) from #tmp b where b.f1 = 表.f1
dspxxxxxxx1
dspxxxxxxx2
.
.
dsp.......n
我想把主键改成这个模式
update 表 set f1 = left(rtrim(b.f1)+ cast(b.id as varchar(10)),12)
from #tmp b where b.f1 = 表.f1
它应该是连续的,
eg:
第一条记录的主键是
dsp000000001
然后到最后条记录(3000)
dsp000003000
update 表 set f1 = 'dsp'
+ right('000000000'+ltrim(rtrim(cast(b.id as varchar(10)))),9)
from #tmp b where b.f1 = 表.f1