create table tb(a int identity(1,1), b as ('mm'+left('000',3-len(a))+cast(a as varchar)), c varchar(10) ) insert into tb(c) select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g' union all select 'h' union all select 'i' union all select 'j' union all select 'k' union all select 'l' select * from tb drop table tb/* a b c ----------- ----------------------------------- ---------- 1 mm001 a 2 mm002 b 3 mm003 c 4 mm004 d 5 mm005 e 6 mm006 f 7 mm007 g 8 mm008 h 9 mm009 i 10 mm010 j 11 mm011 k 12 mm012 l(所影响的行数为 12 行) */
create table #t(picname varchar(20)) insert into #t select 'mm001.jpg' union all select 'mm002.jpg' union all select 'mm003.jpg' union all select 'mm004.jpg'alter table #t add id int identity(1,1) go update #t set picname=left(picname,2)+right('000'+rtrim(id),3)+right(picname,4)alter table #t drop column id go select * from #t /* picname id -------------------- ----------- mm001.jpg 1 mm002.jpg 2 mm003.jpg 3 mm004.jpg 4 (所影响的行数为 4 行) */ drop table #t
zlp321002(付出最大努力,追求最高成就,享受最佳生活,收获无悔人) 采用的是计算列方式 感觉不大好修改 declare @i int set @i=0 update table_Pqs set col='mm'+ right('000'+cast(@i as varchar),3)+'.jpg',@i=@i+1生成 select top 50 a=identity(int,1,1) into #table_Pqs from sysobjects a,sysobjects b select 'mm'+ right('000'+cast(a as varchar),3)+'.jpg' from #table_Pqs drop table #table_Pqs
这里有个列子
http://blog.csdn.net/roy_88/archive/2006/12/01/1424370.aspx
我只是想批量的这样增加来更名
insert into tb(c)
select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
union all select 'f'
union all select 'g'
union all select 'h'
union all select 'i'
union all select 'j'
union all select 'k'
union all select 'l'
select * from tb
drop table tb/*
a b c
----------- ----------------------------------- ----------
1 mm001 a
2 mm002 b
3 mm003 c
4 mm004 d
5 mm005 e
6 mm006 f
7 mm007 g
8 mm008 h
9 mm009 i
10 mm010 j
11 mm011 k
12 mm012 l(所影响的行数为 12 行)
*/
insert into #t
select 'mm001.jpg'
union all select 'mm002.jpg'
union all select 'mm003.jpg'
union all select 'mm004.jpg'alter table #t add id int identity(1,1)
go
update #t
set picname=left(picname,2)+right('000'+rtrim(id),3)+right(picname,4)alter table #t drop column id
go
select * from #t
/*
picname id
-------------------- -----------
mm001.jpg 1
mm002.jpg 2
mm003.jpg 3
mm004.jpg 4
(所影响的行数为 4 行)
*/
drop table #t
/*
picname
--------------------
mm001.jpg
mm002.jpg
mm003.jpg
mm004.jpg
*/
declare @i int
set @i=0
update tb set 列名=(包含@i的表达式),@i=@i+1
---------------
这个方法好
感觉不大好修改
declare @i int
set @i=0
update table_Pqs set col='mm'+ right('000'+cast(@i as varchar),3)+'.jpg',@i=@i+1生成
select top 50 a=identity(int,1,1) into #table_Pqs from sysobjects a,sysobjects b
select 'mm'+ right('000'+cast(a as varchar),3)+'.jpg' from #table_Pqs
drop table #table_Pqs