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

...全文
60 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
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



34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧