如何用一个表的某一列更新另一个表的某列（字段名相同）
例如：
table1
waybillcode pn qty
-------------------------------
a001 a1 10
a001 a2 15
a001 a3 5
a002 a4 1
a002 a2 5
......
-------------------------------
table2
waybillcode pn qty
-------------------------------
a001 a1 8
a001 a2 15
a001 a3 5
a002 a2 5
......
-------------------------------
如何用table2更新table1中对应的qty？得到结果如下：
tmpTable
waybillcode pn qty
-------------------------------
a001 a1 8
a001 a2 15
a001 a3 5
a002 a4 0
a002 a2 5
......
-------------------------------
[解决办法]
update table1
set qty=b.qty
from table2 b
where waybillcode=b.waybillcode and pn=b.pn
[解决办法]
修改——
update #A
set qty=b.qty
from #A a,#B b
where a.waybillcode=b.waybillcode and a.pn=b.pn
[解决办法]
create table #A
(waybillcode varchar(50),
pn varchar(50),
qty int
)
insert into #A
select 'a001 ', 'a1 ', '10 ' union all select 'a001 ', 'a2 ', '15 ' union all select 'a001 ', 'a3 ', '5 ' union all select 'a002 ', 'a4 ', '1 ' union all select 'a002 ', 'a2 ', '5 '
select * from #A
create table #B
(waybillcode varchar(50),
pn varchar(50),
qty int
)
insert into #B
select 'a001 ', 'a1 ', '8 ' union all select 'a001 ', 'a2 ', '15 ' union all select 'a001 ', 'a3 ', '5 ' union all select 'a002 ', 'a2 ', '5 '
select * from #B
查询
update #A
set qty=b.qty
from #A a,#B b
where a.waybillcode=b.waybillcode and a.pn=b.pn
select * from #A
-------------
waybillcode pn qty
a001 a1 8
a001 a2 15
a001 a3 5
a002 a4 1
a002 a2 5
[解决办法]
update A set A.qty=B.qty
from table1 A, table2 B
where A.waybillcode=B.waybillcode and A.pn=B.pn
[解决办法]
你的table2不存在的记录,更新为0是吗?
可以这样实现
update A set A.qty=0
from table1 A, table2 B
where A.waybillcode!=B.waybillcode or A.pn!=B.pn
update A set A.qty=B.qty
from table1 A, table2 B
where A.waybillcode=B.waybillcode and A.pn=B.pn
