将B表中列作为新列添加到A表中
efund 2004-05-08 06:21:05 将B表中的WT_QTY_R列作为新列WT_QTY_R_bz添加到A表中,条件是CODE_TS 和UNIT_1要分别相等相等,即内联结,B表有两个字段作为主键即CODE_TS 和UNIT_1,它们唯一确定WT_QTY_R。我做了一点,没做出来,请高手教教我。
create table RF_WT_QTY_TEMP
(
CODE_TS CHAR(20),
G_NAME NVARCHAR,
G_MODEL NVARCHAR,
UNIT_1 VARCHAR(50),
WT_QTY_R NUMERIC
)
create table IM_2003_ENTRY_WORKING_SAMPLE_temp
(
entry_id char(20),
CODE_TS CHAR(20),
G_NAME NVARCHAR,
G_MODEL NVARCHAR,
UNIT_1 VARCHAR(50),
WT_QTY_R NUMERIC
)
insert RF_WT_QTY_TEMP(CODE_TS,UNIT_1,WT_QTY_R)
select 'ter','gfd',12
union all select 'f','b',5
union all select 'a','q',7
union all select 'f','e',11
union all select 'v','r',9
insert IM_2003_ENTRY_WORKING_SAMPLE_temp(entry_id,CODE_TS,UNIT_1,WT_QTY_R)
select 'd3','ter','gfd',13
union all select 'rgt5','f4','b',5
union all select '3gg','a','q',7
union all select 'g5de','f6','e',11
union all select 'fg','v','r',10
alter table IM_2003_ENTRY_WORKING_SAMPLE_temp add WT_QTY_R_bz int
select b.WT_QTY_R,b.id into t from RF_WT_QTY_TEMP b,IM_2003_ENTRY_WORKING_SAMPLE_temp a where a.CODE_TS=b.CODE_TS and a.unit_1=b.unit_1
update IM_2003_ENTRY_WORKING_SAMPLE_temp set WT_QTY_R_bz = (select WT_QTY_R from t,IM_2003_ENTRY_WORKING_SAMPLE_temp a where a.id=t.id)