--生成测试数据
create table #t(id int identity(1,1),name varchar(20))
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaaf'
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaag'--执行更新操作
update a
set
a.name = a.name + char(96+(select count(*) from #t where id<=a.id and name=a.name))
from
#t a
where
exists(select 1 from #t where name=a.name and id!=a.id)
--查看更新结果
select * from #t
--输出结果
id name
--- -----
1 aaaba
2 aaaca
3 aaada
4 aaaea
5 aaafa
6 aaabb
7 aaacb
8 aaadb
9 aaaeb
10 aaabc
11 aaacc
12 aaadc
13 aaaec
14 aaaed
15 aaaee
16 aaag
create table #t(id int identity(1,1),name varchar(20))
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaaf'
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaab'
insert into #t(name) select 'aaac'
insert into #t(name) select 'aaad'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaae'
insert into #t(name) select 'aaag'--执行更新操作
update a
set
a.name = a.name + char(96+(select count(*) from #t where id<=a.id and name=a.name))
from
#t a
where
exists(select 1 from #t where name=a.name and id!=a.id)
--查看更新结果
select * from #t
--输出结果
id name
--- -----
1 aaaba
2 aaaca
3 aaada
4 aaaea
5 aaafa
6 aaabb
7 aaacb
8 aaadb
9 aaaeb
10 aaabc
11 aaacc
12 aaadc
13 aaaec
14 aaaed
15 aaaee
16 aaag
id name
--- ------
1 aaaba1
2 aaaca1
3 aaada1
4 aaaea1
5 aaafa1
6 aaaba2
7 aaaca2
8 aaada2
9 aaaea2
10 aaaba3
11 aaaca3
...
SET a.material_name = a.material_name + char(65) + str
(SELECT COUNT (*)
FROM MD_MATERIAL
WHERE row_id <= a.row_id AND material_name = a.material_name)
FROM MD_MATERIAL a
WHERE EXISTS
(SELECT 1
FROM MD_MATERIAL
WHERE material_name = a.material_name AND row_id != a.row_id)
UPDATE a
SET a.material_name = a.material_name + char(65) + str
(SELECT COUNT (*)
FROM MD_MATERIAL
WHERE row_id <= a.row_id AND material_name = a.material_name)
FROM MD_MATERIAL a
WHERE EXISTS
(SELECT 1
FROM MD_MATERIAL
WHERE material_name = a.material_name AND row_id != a.row_id)
set #t.name=Ta.newname
from #t,
(
select id,name+'-'+char(96+
(select count(1) from #t where #t.name=A.name and #t.id <=A.id)) as newname
from #t A
) Ta
where #t.id=Ta.id