34,594
社区成员
发帖
与我相关
我的任务
分享
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
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
--方法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
'
exec sp_MSforeachtable
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
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
exec sp_MSforeachtable
'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
'
ITEM_CODE CODE
#98001 #98001-1
#98002 #98002-2
#20001 #20001-1
#60004 #60004-2