--添加新编号列
alter table fix add fixno_new int identity(20050001,1)
--删除原编号列
alter table fix drop column fixno
--重新命名新编号列
exec sp_rename 'fix.fixno_new','fixno',['column']
好把。表名是修理表(fix) 字段 fixno (修理编号) appdate (修理日期)。现在里面的编号都已经存在了,我只是想把他改成新的格式要是一个一个改会很慢,我想用sql语句一次性都改掉。这是我写的编码不过现在还是有问题的。
declare @fetid1 int
declare @fetid2 int
declare @id int
declare @no char(4)
declare @newno char(4)
declare @recacle int
set @no = '0000'
set @id = 1
set @newno = ''
set @fetid1 = 138
select @recacle = count(fixno) from fix
select @newno = left(@no,4-len(@id)) + cast(@id as char)
begin
while @id <= @recacle
if @fetid1<>@fetid2
continue
update fix
set fixno='2005'+@newno
from fix
where id = @fetid1
select @fetid2 = id from fix
set @fetid1 = @fetid1 + 1
set @id = @id + 1
end