将B表中列作为新列添加到A表中

[efund]

将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)

[FutureSa]

alter table IM_2003_ENTRY_WORKING_SAMPLE_temp add WT_QTY_R_bz int

update IM_2003_ENTRY_WORKING_SAMPLE_temp
set WT_QTY_R_bz = t.WT_QTY_R
from RF_WT_QTY_TEMP t,IM_2003_ENTRY_WORKING_SAMPLE_temp a
where a.CODE_TS =t.CODE_TS
and a.UNIT_1 =t.UNIT_1

• 打赏
• 举报

回复 赞

[FutureSa]

alter table IM_2003_ENTRY_WORKING_SAMPLE_temp add WT_QTY_R_bz int
go

update IM_2003_ENTRY_WORKING_SAMPLE_temp
set WT_QTY_R_bz = t.WT_QTY_R
from RF_WT_QTY_TEMP t,IM_2003_ENTRY_WORKING_SAMPLE_temp a
where a.CODE_TS =t.CODE_TS
and a.UNIT_1 =t.UNIT_1

• 打赏
• 举报

回复 赞

[internetcsdn]

刚回来,吃饭去先

• 打赏
• 举报

回复 赞

[zjcxc]

alter table IM_2003_ENTRY_WORKING_SAMPLE_temp add WT_QTY_R_bz int
go

update a
set WT_QTY_R_bz=b.WT_QTY_R
from IM_2003_ENTRY_WORKING_SAMPLE_temp a
join RF_WT_QTY_TEMP b on a.CODE_TS=b.CODE_TS and a.unit_1=b.unit_1

select * from IM_2003_ENTRY_WORKING_SAMPLE_temp

• 打赏
• 举报

回复 赞

[zjcxc]

--测试

--测试数据
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
go

--添加新列
alter table IM_2003_ENTRY_WORKING_SAMPLE_temp add WT_QTY_R_bz int
go

--进行更新
update a
set WT_QTY_R_bz=b.WT_QTY_R
from IM_2003_ENTRY_WORKING_SAMPLE_temp a
join RF_WT_QTY_TEMP b on a.CODE_TS=b.CODE_TS and a.unit_1=b.unit_1

--显示更新结果
select * from IM_2003_ENTRY_WORKING_SAMPLE_temp
go

--删除测试
drop table IM_2003_ENTRY_WORKING_SAMPLE_temp,RF_WT_QTY_TEMP


/*--测试结果
entry_id CODE_TS G_NAME G_MODEL UNIT_1 WT_QTY_R WT_QTY_R_bz
--------- --------- ------ ------- ----------- --------- -------------
d3 ter NULL NULL gfd 13 12
rgt5 f4 NULL NULL b 5 NULL
3gg a NULL NULL q 7 7
g5de f6 NULL NULL e 11 NULL
fg v NULL NULL r 10 9

（所影响的行数为 5 行）
--*/

• 打赏
• 举报

回复 赞

[internetcsdn]

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

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
go

update IM_2003_ENTRY_WORKING_SAMPLE_temp
set WT_QTY_R_bz = t.WT_QTY_R
from RF_WT_QTY_TEMP t join IM_2003_ENTRY_WORKING_SAMPLE_temp a
on a.CODE_TS =t.CODE_TS
and a.UNIT_1 =t.UNIT_1


--select * from RF_WT_QTY_TEMP
select * from IM_2003_ENTRY_WORKING_SAMPLE_temp

drop table RF_WT_QTY_TEMP
drop table IM_2003_ENTRY_WORKING_SAMPLE_temp

/*
--------------------------------------------------------------------------------------
d3 ter NULL NULL gfd 13 12
rgt5 f4 NULL NULL b 5 NULL
3gg a NULL NULL q 7 7
g5de f6 NULL NULL e 11 NULL
fg v NULL NULL r 10 9

*/

• 打赏
• 举报

回复 赞

[internetcsdn]

太慢.写时已回了几贴

• 打赏
• 举报

回复 赞

[luckds]

恩 这样应该可以的

begin tran

update IM_2003_ENTRY_WORKING_SAMPLE_temp set WT_QTY_R_bz=b.WT_QTY_R
from IM_2003_ENTRY_WORKING_SAMPLE_temp a,RF_WT_QTY_TEMP b
where a.CODE_TS=b.CODE_TS and a.UNIT_1=b.UNIT_1

commit

• 打赏
• 举报

回复 赞