最近公司转型要改变系统的一些设计,公司用一个ERP系统,有一个数据库,数据库里面有几百个数据表,要将其中所有的成品物料编码修改,其中一个基本资料表UN_ITEM1,里面记录了整个数据库用到的所有的成品物料编码。它的结构如下:ITEM_CODE DES UNIT PRICE
#98001 卡片1 PCS 0.1
#98002 卡片2 PCS 0.4
#20001 卡片3 PCS 0.3
#60004 卡片X PCS 0.7
#70004 卡片X PCS 0.7但是其他的表跟UN_ITEM1不是以主键关联,而是直接写ITEM_CODE进去的,其他所有的表的结构都类似:SOID ITEM_CODE INDATE QUANTITY
SO10-1001 #98001 2011-09-15 500
SO10-1002 #98002 2011-10-25 900
SO10-1003 #98002 2011-10-26 700
SO10-1004 #98003 2011-08-26 800现在的问题是:如果基本资料表UN_ITEM1里面的ITEM_CODE全部要分成3类,分别在后加上-1或-2或-3。比如要变成#98001-1,#98002-2,#20001-1,#60004-2,#70004-3,那么要怎么修改其他几百个数据表里面的所有ITEM_CODE呢?
大家有什么好的建议吗?
数据库环境:sql 2000
#98001 卡片1 PCS 0.1
#98002 卡片2 PCS 0.4
#20001 卡片3 PCS 0.3
#60004 卡片X PCS 0.7
#70004 卡片X PCS 0.7但是其他的表跟UN_ITEM1不是以主键关联,而是直接写ITEM_CODE进去的,其他所有的表的结构都类似:SOID ITEM_CODE INDATE QUANTITY
SO10-1001 #98001 2011-09-15 500
SO10-1002 #98002 2011-10-25 900
SO10-1003 #98002 2011-10-26 700
SO10-1004 #98003 2011-08-26 800现在的问题是:如果基本资料表UN_ITEM1里面的ITEM_CODE全部要分成3类,分别在后加上-1或-2或-3。比如要变成#98001-1,#98002-2,#20001-1,#60004-2,#70004-3,那么要怎么修改其他几百个数据表里面的所有ITEM_CODE呢?
大家有什么好的建议吗?
数据库环境:sql 2000
大家有什么好的建议吗?-------------
更新为-1或-2或-3的判断规则是?
我是这样想的,新建一个表,有2个字段,一个ITEM_CODE,一个是修改后的CODE,ITEM_CODE CODE
#98001 #98001-1
#98002 #98002-2
#20001 #20001-1
#60004 #60004-2然后根据这个关系,修改其他所有的数据表,不知道这样行不?
'if exists(select * from syscolumns where ID=OBJECT_ID(''?'') and Name=''ITEM_CODE'') and ''?''<>''UN_ITEM1''
update b
set ITEM_CODE=a.Code
from UN_ITEM1 as a,? as b
where a.ITEM_CODE=b.ITEM_CODE
'
可以这样写,你自己测测,我这里没有SQL2000环境
N'if exists(select 1 from syscolumns where ID=OBJECT_ID(''?'') and Name=''ITEM_CODE'' and ID<>OBJECT_ID(''UN_ITEM1''))
begin
update b
set ITEM_CODE=a.Code
from UN_ITEM1 as a,? as b
where a.ITEM_CODE=b.ITEM_CODE
end
'
Create table UN_ITEM1(ITEM_CODE nvarchar(20),Code nvarchar(20))
Create table T(ITEM_CODE nvarchar(20))
insert T select '#1000'
insert UN_ITEM1 select '#1000','#1000-1'
select * from T
你自己把用一个测试的DB测一下,我这里是08环境测不了
N'if exists(select 1 from syscolumns where ID=OBJECT_ID(''?'') and Name=''ITEM_CODE'' and ID<>OBJECT_ID(''UN_ITEM1''))
begin
exec(''update b
set ITEM_CODE=a.Code
from UN_ITEM1 as a,? as b
where a.ITEM_CODE=b.ITEM_CODE'')
end
'
Create table UN_ITEM1(ITEM_CODE nvarchar(20),Code nvarchar(20))
Create table T(ITEM_CODE nvarchar(20))
insert T select '#1000'
insert UN_ITEM1 select '#1000','#1000-1'
select * from T
上面不是寫了麼?
exec sp_MSforeachtable--用這個查找所有ITEM_CODE字段的表並且,表名不為UN_ITEM1
--方法1(SAL2000/SQL2005/SQL2008)
exec sp_MSforeachtable
N'if exists(select 1 from syscolumns where ID=OBJECT_ID(''?'') and Name=''ITEM_CODE'' and ID<>OBJECT_ID(''UN_ITEM1'') and OBJECTPROPERTY(ID,''IsTable'')=1)
begin
update b
set ITEM_CODE=a.Code
from UN_ITEM1 as a,? as b
where a.ITEM_CODE=b.ITEM_CODE
end
'
--方法1(SAL2000/SQL2005)
exec sp_MSforeachtable
'if exists(select * from syscolumns where ID=OBJECT_ID(''?'') and Name=''ITEM_CODE'' and ''?''<>''UN_ITEM1'' and OBJECTPROPERTY(ID,''IsTable'')=1)
update b
set ITEM_CODE=a.Code
from UN_ITEM1 as a,? as b
where a.ITEM_CODE=b.ITEM_CODE
'
消息 207,级别 16,状态 3,第 1 行
列名 'ID' 无效。
消息 207,级别 16,状态 3,第 1 行
列名 'Name' 无效。
消息 207,级别 16,状态 3,第 1 行
列名 'ID' 无效。
消息 207,级别 16,状态 1,第 2 行
列名 'ITEM_CODE' 无效。
消息 207,级别 16,状态 3,第 2 行
列名 'Code' 无效。
注意調用方法,注意版本SQL08的動態啊
把你改動的貼出來吧,估計你改錯位置了
1、先在UN_ITEM1上新增字段名:ITEM_NewCODE,把该字段赋值为新的物料代码,比如:
update UN_ITEM1
set ITEM_NewCODE=ITEM_CODE+'-1'2、更新存在ITEM_CODE字段的表数据
select fid=IDENTITY(int, 1, 1), object_name(t1.id) ftablename into #date1
from syscolumns t1 inner join sysobjects t2 on OBJECT_NAME(t1.id)=t2.name
where t1.name= 'ITEM_CODE' and t2.xtype='u' not t2.name='UN_ITEM1'declare @sql VarChar(8000)
declare @table VarChar(40)
declare @fid int
set @fid=1
while @fid<=(select max(fid) from #date1)
begin
set @table=(select top 1 ftablename from #date1 where fid=@fid)
set @sql = ''
set @sql = @sql+' update t1'
set @sql = @sql+' set t1.ITEM_CODE=t2.ITEM_NewCODE'
set @sql = @sql+' from '+@table+' t1 inner join UN_ITEM1 t2 on t1.ITEM_CODE=t2.ITEM_CODE'
exec(@sql)
set @fid=@fid+1
end
drop table #date1