有表a，内有字段a1,a2,a3···b1,b2,b3····。要求更新以a开头的部分字段（不包含a1,a2,a3）的值
假定把以a开头的部分列（不包含a1,a2,a3）的所有值改为100。
难点我认为是where子句怎样做列名的判断，还有set子句的循环更新。 展开
create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c) <> 0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c), '')
end
insert @t(col) values (@c)
return
end
go
select '11111111111111111111111111111111111' as service_id,
'11111111111111111111111111111111111111111111111111111' as name into #aa
delete from #aa
declare @service_id nvarchar(50)
declare @number nvarchar(50)
declare @name nvarchar(50)
declare @name_temp nvarchar(50)
DECLARE temp_1 cursor for
select service_id,number from a
open temp_1
fetch next from temp_1 into @service_id,@number
while @@FETCH_STATUS=0
begin
select @name=''
DECLARE temp_2 cursor for
select name from dbo.f_split(@number,',') a join b b on a.col = b.number
open temp_2
fetch next from temp_2 into @name_temp
while @@FETCH_STATUS=0
begin
if @name=''
begin
select @name = LTRIM(RTRIM(@name_temp))
end
else
begin
select @name = LTRIM(RTRIM(@name))+','+ LTRIM(RTRIM(@name_temp))
end
fetch next from temp_2 into @name_temp
end
close temp_2
deallocate temp_2
end
insert into #aa(service_id,name)values(@service_id,@name)
close temp_1
deallocate temp_1
select * from #aa
drop table #aa
drop function f_split
select*from a
select*from b
select column_name
FROM user_tab_columns
WHERE table_name = 'A'
and column_name like 'A%'
and column_name not in('A1','A2','A3')
这样就可以取到想更新的列了，必须要写存储过程来循环判断列然后更新的，按照这个思路来，肯定没错
是存储过程，参数说明：
tbName 表名称；
acolumn 需要模糊查找的列的关键字；
notlike 需要排除的字符串，逗号分割符【串中不可有值代逗号的】；
upObj 字段更改成的值，int类型
执行范例：exec Mypro '表a','a','a1,a2,a3',100
create proc Mypro(
@tbName varchar(200),
@acolumn varchar(200),
@notlike varchar(8000),
@upObj int
)
as
begin
declare @tbid int,@sql1 varchar(8000),@vchar varchar(8000),@sql varchar(8000)
select @tbid=id from sysobjects where name=@tbName
select @sql='update '+@tbName+'set ',@vchar='',@sql1='',@notlike=@notlike+','
while len(@notlike)>0
begin
select @sql1='not like '''+left(@notlike,charindex(',',@notlike))+'[%]'' and '
select @notlike=right(@notlike,len(@notlike)-charindex(',',@notlike))
if len(@notlike)=0
select @sql1=left(@sql1,len(@sql1)-4)
end
select @sql1='select @vchar=@vchar+name+''='+cast(@upObj as varchar(15))+','' from syscolumns where id='+@tbid+' name like '''+@acolumn+'[%]'' and '+@sql1
exec (@sql1)
select @vchar=left(@vchar,len(@vchar)-1)
select @sql=@sql+@vchar
exec (@sql)
end
根据系统表中取得需要更新的字段名，执行更新
--未测试，报错贴错误提示
select column_name
FROM user_tab_columns
WHERE table_name = 'A'
and column_name like 'A%'
and column_name not in('A1','A2','A3')
再循环更新就可以了
