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

...全文
37 8 点赞 打赏 收藏 举报
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
luckds 2004-05-08

恩 这样应该可以的

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

  • 打赏
  • 举报
回复
internetcsdn 2004-05-08
太慢.写时已回了几贴
  • 打赏
  • 举报
回复
internetcsdn 2004-05-08
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

*/

  • 打赏
  • 举报
回复
zjcxc 元老 2004-05-08
--测试

--测试数据
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 行)
--*/
  • 打赏
  • 举报
回复
zjcxc 元老 2004-05-08

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
  • 打赏
  • 举报
回复
internetcsdn 2004-05-08
刚回来,吃饭去先
  • 打赏
  • 举报
回复
FutureSa 2004-05-08
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
  • 打赏
  • 举报
回复
FutureSa 2004-05-08
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



  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-05-08 06:21
社区公告
暂无公告