row_number() over(order by id)
--------------------------------------------------------------------------
if object_id('[test]') is not null drop table [test]
go
create table [test] (id varchar(4))
insert into [test]
select '0001' union all
select '0001' union all
select '0001' union all
select '0001' union all
select '0001'
DECLARE @I INT
SELECT @I=MIN(ID)-1 FROM test
UPDATE test SET @I=@I+1,id=RIGHT('0000'+LTRIM(@I),4)
SELECT * FROM TEST
--------------------------------------------------------------------------
DECLARE @I INT
SELECT @I=MIN(ID) FROM test
UPDATE test SET id='800-'+RIGHT('0000'+LTRIM(@I-1),4),@I=@I+1
————————————————————————————————
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test
(
id int
)
GO
INSERT test
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1
--查询
declare @i int
set @i=1
declare cur cursor for select id from test
open cur
fetch cur
while @@fetch_status=0
begin
update test set id=@i where current of cur
set @i=@i+1
fetch cur
end
close cur
deallocate cur
select * from test