我有一个对照表:ITM_A ITM_B
001 A001
002 A001
003 C030
004 X010
006 B011我想从数据库中找出所有包含字段 ITM 的表(这个知道怎么做),然后在找到的表中,如ITM为上表中ITM_A的值都改为ITM_B的值。
001 A001
002 A001
003 C030
004 X010
006 B011我想从数据库中找出所有包含字段 ITM 的表(这个知道怎么做),然后在找到的表中,如ITM为上表中ITM_A的值都改为ITM_B的值。
set @sql = ''select @sql=@sql
+ ' update ' + name
+ ' set ITM = 对照表.ITM_B
from 对照表
where ' + name + '.itm = 对照表.ITM_A '
from sysobjects
where id in
(select id from syscolumns where name = 'code')exec (@sql)
declare @sql varchar(max)
set @sql = ''select @sql=@sql
+ ' update ' + name
+ ' set ITM = 对照表.ITM_B
from 对照表
where ITM = 对照表.ITM_A '
from sysobjects
where id in
(select id from syscolumns where name = 'ITM')exec (@sql)
select t.name, c.name from sysobjects t join syscolumns c on t.id = c.id where c.name = 'ITM' and t.type = 'U' order by t.name
open colcur
fetch first from colcur into @table_name, @column_name
while @@fetch_status = 0
begin
set @str_sql = 'update a set a.itm = b.itm_b from ' + @table_name + ' a join 对照表 b on a.' + @column_name + ' = b.itm_a'
exec (@str_sql)
fetch next from colcur into @table_name, @column_name
end
close colcur
deallocate colcur